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
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
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)
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
VALUES (3, 500, 'G.I', 'JOE', 'New York', 'USA', NOW(),NOW())