3

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;
  • For starters, your function is named gi_joe_insert(), but you call insert_gi_joe() in the rule. And NEW has a special meaning in plpgsql and should not be used as parameter name. – Erwin Brandstetter Jan 15 '15 at 22:24
  • The reason why the fiddle isn't working is that SQLFiddle doesn't understand the dollar quoting and thus chokes on the embedded ; inside the function. You need to switch the fiddle to use a different statement terminator (and of course change your script to use that). – a_horse_with_no_name Jan 16 '15 at 7:55
  • Allright didnt know that, updated the fiddle! – DanFritz Jan 16 '15 at 8:10
  • @a_horse_with_no_name: Not only dollar-quoting JDBC butchers statements at the query terminator, which also kills a rule with multiple statements and ; inside. (like in my answer). Details here. The workaround is a different query terminator (that's doesn't appear in your code otherwise, but you don't have to exchange ;. JDBC will just execute the whole script at once - and you only get results from the last statement back! (May also be confusing.) – Erwin Brandstetter Jan 16 '15 at 10:17
  • @ErwinBrandstetter: the parser ignores ; inside a string literal and the Postgres JDBC driver does understand dollar quoting, so you can run select $$foo;bar$$ using JDBC without any problems (It's the same as running select 'foo;bar') SQLFiddle parses the script before sending it to the database but it doesn't check for quoted terminators (so using standard SQL single quotes also doesn't work) and of course it doesn't understand dollar quoting either. – a_horse_with_no_name Jan 16 '15 at 10:39
2

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;
  • Thanks, In the mean time I solved my own question, I used triggers instead of rules so I could return the complete row. – DanFritz Jan 16 '15 at 10:12
  • @DanFritz: Triggers are a good alternative, often simpler to handle. But I still thought your question about the rule was worth looking into. – Erwin Brandstetter Jan 16 '15 at 10:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.