How to return a newly created id from a Postgres function?

Question

I am working on creating partitions for a table in Postgres and have the following function:

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;

I am using this function with a trigger:

CREATE TRIGGER testing_partition_insert_trigger
BEFORE INSERT ON testing_partition
FOR EACH ROW EXECUTE PROCEDURE create_partition_and_insert();

The table for testing:

CREATE TABLE testing_partition(patent_id BIGINT, date DATE) WITH ( OIDS=FALSE);

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:

RETURNING patent_id INTO newid;

It seem Postgres does not support returning values this way:

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

buddy take a look on this http://stackoverflow.com/questions/33212417/java-postgresql-last-inserted-id-on-insertion-not-getting. Explained in detail. — androidGenX, Feb 16 '16 at 09:16
`INTO` needs to be part of the `EXECUTE` statement, not part of the `INSERT`. Move it outside of the query string. — Nick Barnes, Feb 16 '16 at 10:43
Thanks, Nick, would you mind giving me an example? I think I have it part of INSERT — Istvan, Feb 16 '16 at 12:41

score 0 · Accepted Answer · answered Feb 17 '16 at 08:10

The solution is to add RETURNING patent_id without INTO at the and of the INSERT and do the same thing when issuing the actual INSERT.

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) || ').* RETURNING patent_id;';
      RETURN NULL;
    END;
  $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

Using the extended function works the following way:

# insert into testing_partition values (1, '2011-01-11') returning patent_id ;
 patent_id
-----------
         1
(1 row)

[This other answer to a similar question](https://stackoverflow.com/a/33116347/372643) might be of interest, in particular `EXECUTE format('INSERT INTO %s SELECT ($1).*', _partition) USING NEW;` — Bruno, Nov 09 '17 at 01:01

How to return a newly created id from a Postgres function?

1 Answers1