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
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.
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) );
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;