PostgreSQL – How to Return a Record from Function Executed by INSERT/UPDATE Trigger

database-designfunctionsplpgsqlpostgresqlview

I'm having some issue when I'm trying to join two tables in a single view (for migrating code we want to split one big record into two records). I'm using Postgres 9.4!
Here is an SQL fiddle.

The fiddle doesn't work for some reason (not really sure if advanced stuff is supported). So basically I have a custom function that first create the gi record, cast the primary ID from the gi record in a variable and then I use this to query some external table and use the result to finally create a joe record.

Now I need to support the following syntax:

INSERT INTO gi_joe(weapon_type,weapon_fire_power,first_name,last_name,city
                  ,country,created_at,updated_at) 
VALUES (3, 500, 'G.I', 'JOE', 'New York', 'USA', NOW(),NOW()) RETURNING id;

This is a typical insert that is done in our application layer (it inserts a record and then uses the returning id). I'm not able to run this query because I get the following error:

ERROR: cannot perform INSERT RETURNING on relation "gi_joe"
SQL state: 0A000
Hint: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.

How can I support the query with the RETURNING set from a custom function. Or is there a way to do everything in one INSERT (with an optional RETURNING field). Please note that my example is simplified.

Table setup:

CREATE TABLE gi
(
  id serial NOT NULL,
  weapon_type_id integer,
  fire_power integer,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT gi_pkey PRIMARY KEY (id)
);

CREATE TABLE joe
(
  id serial NOT NULL,
  first_name character varying,
  last_name character varying,
  city character varying,
  country character varying,
  gi_id integer,
  external_id integer,
  created_at timestamp without time zone NOT NULL,
  updated_at timestamp without time zone NOT NULL,
  CONSTRAINT joe_pkey PRIMARY KEY (id)
);

CREATE TABLE external
(
  id serial NOT NULL,
  joe_id integer,
  CONSTRAINT external_pkey PRIMARY KEY (id)
);

View:

CREATE OR REPLACE VIEW gi_joe AS (
  SELECT  gi.id AS id, 
    gi.weapon_type_id as weapon_type, 
    gi.fire_power as weapon_fire_power,
    joe.first_name as first_name,
    joe.last_name as last_name,
    joe.city as city,
    joe.country as country,
    joe.external_id as external_id,
    gi.created_at as created_at,
    gi.updated_at as updated_at
  FROM gi LEFT OUTER JOIN joe ON gi.id = joe.gi_id
);

Function used in rule:

CREATE OR REPLACE FUNCTION gi_joe_insert(new_gi_joe gi_joe) RETURNS integer AS $$
  DECLARE temp_gi_id integer;
  DECLARE temp_joe_id integer;
  BEGIN
    INSERT INTO gi(weapon_type_id, fire_power, created_at, updated_at)
      VALUES (new_gi_joe.weapon_type, new_gi_joe.weapon_fire_power, new_gi_joe.created_at, new_gi_joe.updated_at)
      RETURNING id INTO temp_gi_id;

    SELECT joe_id INTO temp_joe_id FROM external WHERE id = new_gi_joe.external_id LIMIT 1;

    INSERT INTO joe(first_name, last_name, city, country, gi_id, external_id,
                    created_at,updated_at)
      VALUES (NEW.first_name, new_gi_joe.last_name, new_gi_joe.city, new_gi_joe.country, temp_gi_id,
            temp_joe_id, new_gi_joe.created_at, new_gi_joe.updated_at);
  RETURN temp_gi_id;
  END;
$$ LANGUAGE plpgsql;

Rule, insert with the returning id:

CREATE OR REPLACE RULE custom_insert_gi_joe AS ON INSERT TO gi_joe DO INSTEAD (
  SELECT gi_joe_insert(NEW) AS id;
);

INSERT INTO gi_joe(weapon_type,weapon_fire_power,first_name,last_name,city
                  ,country,created_at,updated_at) 
VALUES (3, 500, 'G.I', 'JOE', 'New York', 'USA', NOW(),NOW()) RETURNING id;

Best Answer

The hint of the error message is right on target:

You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clause.

Bold emphasis mine. Per documentation:

RETURNING queries on the view will be rejected if there is no RETURNING clause in any available rule.

Don't confuse this with the RETURNS clause of your function. Not the same thing.

Postgres looks for an actual RETURNING clause of an INSERT, UPDATE or DELETE statement. It has to be a plain SQL statement. There can only be one RETURNING clause, even if there are multiple statements in the rule:

furthermore there can be at most one RETURNING clause among all the rules for the same event.

It will only be executed if the outer call has a RETURNING clause, too.

Since you are inserting into two tables, it gets slightly complicated. Either one of the two INSERT statements in the rule only has the columns of one target table at it's disposal. You would need to select from the other table (or from the view itself) in the RETURNING clause to provide a complete row.

Also, the returned value from the serial column cannot be stored in a variable, since there are no variables in plain SQL. I solved it with lastval() instead.

And NEW is not visible in a CTE, so you can't chain data-modifying CTE either.

But it can be done anyway:

CREATE OR REPLACE RULE custom_insert_gi_joe AS
ON INSERT TO gi_joe DO INSTEAD (

 INSERT INTO gi(weapon_type_id, fire_power, created_at, updated_at)
 VALUES (NEW.weapon_type, NEW.weapon_fire_power, NEW.created_at, NEW.updated_at);

 INSERT INTO joe(first_name, last_name, city, country
               , gi_id, external_id, created_at, updated_at)
 VALUES(NEW.first_name, NEW.last_name, NEW.city, NEW.country, lastval()
      ,(SELECT joe_id FROM external WHERE id = NEW.external_id LIMIT 1)
      , NEW.created_at, NEW.updated_at)
 RETURNING
   joe.gi_id,
   NULL::int,
   NULL::int,
   joe.first_name,
   joe.last_name,
   joe.city,
   joe.country,
   joe.external_id,
   NULL::timestamp,
   NULL::timestamp
);

SQL Fiddle.

I didn't bother to provide the complete row and just filled in NULL values for missing columns from gi, cast to respective types. This could be solved, too, but since you are only interested in the id column, it would be cost for no gain.

Now, this works as expected:

INSERT INTO gi_joe(weapon_type,weapon_fire_power,first_name,last_name,city
                  ,country,created_at,updated_at) 
VALUES (3, 500, 'G.I', 'JOE', 'New York', 'USA', NOW(),NOW())
RETURNING id;
Related Question