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;
Best Answer
RETURN
isn't a valid command in a rule definition; you can only useSELECT
,INSERT
,UPDATE
,DELETE
, andNOTIFY
.You can create an
ALSO
rule to return the value of the new row, something like this:Note that you can do something identical by just adding the RETURNING clause to the INSERT statement: