title: How to return a newly created id from a Postgres function?
body: <p>I am working on creating partitions for a table in Postgres and have the following function:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION create_partition_and_insert() RETURNS trigger AS
    $BODY$
      DECLARE
        partition_date TEXT;
        partition TEXT;
      BEGIN
        partition_date := to_char(NEW.date,'YYYY_MM_DD');
        partition := TG_RELNAME || '_' || partition_date;
        IF NOT EXISTS(SELECT relname FROM pg_class WHERE relname=partition) THEN
          RAISE NOTICE 'A partition has been created %',partition;
          EXECUTE 'CREATE TABLE ' || partition || ' (check (date = ''' || NEW.date || ''')) INHERITS (' || TG_RELNAME || ');';
        END IF;
        EXECUTE 'INSERT INTO ' || partition || ' SELECT(' || TG_RELNAME || ' ' || quote_literal(NEW) || ').*;';
        RETURN NULL;
      END;
    $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  </code></pre>
  
  <p>I am using this function with a trigger:</p>
  
  <pre><code>CREATE TRIGGER testing_partition_insert_trigger
  BEFORE INSERT ON testing_partition
  FOR EACH ROW EXECUTE PROCEDURE create_partition_and_insert();
  </code></pre>
  
  <p>The table for testing:</p>
  
  <pre><code>CREATE TABLE testing_partition(patent_id BIGINT, date DATE) WITH ( OIDS=FALSE);
  </code></pre>
  
  <p>Is there a way to return the patent_id from the function? Right now it returns null. I was trying to extend the function with:</p>
  
  <pre><code>RETURNING patent_id INTO newid;
  </code></pre>
  
  <p>It seem Postgres does not support returning values this way:</p>
  
  <pre><code>ERROR:  syntax error at or near "INTO"
  LINE 1: ...rtition '(111,2018-01-11)').* RETURNING patent_id INTO newid...
                                                               ^
  QUERY:  INSERT INTO testing_partition_2018_01_11 SELECT(testing_partition '(111,2018-01-11)').* RETURNING patent_id INTO newid;
  CONTEXT:  PL/pgSQL function create_partition_and_insert() line 12 at EXECUTE statement
  </code></pre>