title: INSERT RETURNING and partitioning POSTGRESQL
body: <p>Recently I've worked with partition table on portgres. I have some problems about <code>INSERT RETURNING id</code> . This is my trigger function and trigger:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION f_port_vessels_insert_trigger_auto()
    RETURNS TRIGGER AS $$
  DECLARE
    insert_sql TEXT;
    week_num INTEGER;
    year_num INTEGER;
    partition_table VARCHAR;
    prefix_partition_table VARCHAR :='port_vessels_p_';
  BEGIN
    SELECT CAST (EXTRACT(WEEK FROM NEW.created_at) AS INTEGER) INTO week_num;
    SELECT CAST (EXTRACT(YEAR FROM NEW.created_at) AS INTEGER) INTO year_num;
    partition_table :=  prefix_partition_table || year_num || '_' || week_num;
    insert_sql := 'INSERT INTO ' || partition_table || ' VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9)';
    EXECUTE insert_sql
    USING NEW.id, NEW.port_id, NEW.vessel_id,
      NEW.last_position_updated_at, NEW.nav_status_code,
      NEW.created_at, NEW.updated_at, NEW.berth_id,
      NEW.berth_shift_id;
    RETURN NULL;
  END;
  $$
  LANGUAGE plpgsql;
  
  CREATE TRIGGER f_port_vessels_insert_trigger
  BEFORE INSERT ON port_vessels
  FOR EACH ROW EXECUTE PROCEDURE f_port_vessels_insert_trigger_auto();
  </code></pre>
  
  <p>I google all of them, and see a solution create a view, created trigger  <code>insert instead on the view</code> instead of table. I think it's fine if we working only on the db, but I use Active Record in Rails, I don't want to change my model much more, so anyone experiences this situation? Or have another way to solve this.</p>