Re: How to insert into 2 tables from a view?

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(at)gmail(dot)com> 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 clause
>
> We 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 AS
> SELECT table1_id, table1_field_1, 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 (
> in_table1_id, in_table1_field1
> )
> RETURNING table1_id
> INTO 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_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);
>
> Thanks,
>
> Chris
>