From: | Chris Hoover <revoohc(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | How to insert into 2 tables from a view? |
Date: | 2014-12-23 18:46:40 |
Message-ID: | CAD-X90QQyKFSdWk5xTSOWamdaWXMBh5rhTkpwZGPdohFid+eRA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Hoover | 2014-12-23 20:00:02 | Re: How to insert into 2 tables from a view? |
Previous Message | pinker | 2014-12-23 15:48:48 | Re: Check if LDAP Authentication is used for user |