Sorry, in my haste to get the example out, a couple of typo's where in the sql.
Correct sql:
BEGIN;
CREATE TABLE table1 (
table1_id SERIAL PRIMARY KEY,
table1_field1 TEXT
);
CREATE TABLE table2 (
table1_id INTEGER NOT NULL PRIMARY KEY REFERENCES table1(table1_id) ON DELETE CASCADE,
table2_field1 TEXT
);
CREATE VIEW orig_table AS
SELECT table1_id, table1_field1, table2_field1
FROM table1
JOIN table2 USING (table1_id);
CREATE FUNCTION orig_table_insert(in_table1_id integer, in_table1_field1 text, in_table2_field1 text)
RETURNS SETOF orig_table
LANGUAGE plpgsql
AS
$BODY$
DECLARE
v_table1_id table1.table1_id%TYPE;
BEGIN
INSERT INTO table1 (
table1_id, table1_field1
) VALUES (
COALESCE(in_table1_id, NEXTVAL('table1_table1_id_seq')), in_table1_field1
)
RETURNING table1_id
INTO v_table1_id;
INSERT INTO table2 (
table1_id, table2_field1
) VALUES (
v_table1_id, in_table2_field1
);
RETURN QUERY SELECT table1_id, table1_field1, table2_field1
FROM orig_table
WHERE table1_id = v_table1_id;
END;
$BODY$;
CREATE RULE orig_table_insert_rule AS
ON INSERT
TO orig_table
DO INSTEAD
SELECT orig_table_insert(NEW.table1_id, NEW.table1_field1, NEW.table2_field1);
COMMIT;
Problem query:
insert into orig_table (table1_field1, table2_field1) values ('field1', 'field2') returning table1_id;
On Tue, Dec 23, 2014 at 1:46 PM, Chris Hoover <revoohc@xxxxxxxxx> wrote:
Hi,I am having a problem trying to figure out.I have two tables behind a view and am trying to figure out how to create the correct insert rule so that inserting into the view is redirected to the two tables. I thought I had is solved using a stored procedure, but doing an insert into view ... returning id causes the insert to fail with this error:ERROR: cannot perform INSERT RETURNING on relation "orig_view"HINT: You need an unconditional ON INSERT DO INSTEAD rule with a RETURNING clauseWe are running pg 9.0 and I think this version of PG is the bottleneck to getting this done. Does anyone know how to get around it? Below is a basic example demonstrating what we are wanting to do.CREATE TABLE table1 (table1_id SERIAL PRIMARY KEY,table1_field1 TEXT);CREATE TABLE table2 (table1_id INTEGER NOT NULL PRIMARY KEY REFERENCES table1(table1_id) ON DELETE CASCADE,table2_field1 TEXT);CREATE VIEW orig_table ASSELECT table1_id, table1_field_1, table2_field1FROM table1JOIN table2 USING (table1_id);CREATE FUNCTION orig_table_insert(in_table1_id integer, in_table1_field1 text, in_table2_field1 text)RETURNS SETOF orig_tableLANGUAGE plpgsqlAS$BODY$DECLAREv_table1_id table1.table1_id%TYPEBEGININSERT INTO table1 (table1_id, table1_field1) VALUES (in_table1_id, in_table1_field1)RETURNING table1_idINTO v_table1_id;INSERT INTO table2 (table1_id, table2_field1) VALUES (v_table_id, in_table2_field1);RETURN QUERY SELECT table1_id, table1_field1, table2_field1FROM orig_tableWHERE table1_id = v_table1_id;END;$BODY$;CREATE RULE orig_table_insert_rule ASON INSERTTO orig_tableDO INSTEADSELECT orig_table_insert(NEW.table1_id, NEW.table1_field1, NEW.table2_field1);Thanks,Chris
