Обсуждение: How to insert into 2 tables from a view?
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
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@gmail.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 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
Chris Hoover-2 wrote > Sorry, in my haste to get the example out, a couple of typo's where in the > sql. Next time, don't quote the entire original wrong query... Anyway, you probably want to create a trigger on your view and do the inserts inside the trigger function. User created CREATE RULE is not usually the correct answer to a problem. David J. -- View this message in context: http://postgresql.nabble.com/How-to-insert-into-2-tables-from-a-view-tp5831876p5831897.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
David G Johnston wrote > > Chris Hoover-2 wrote >> Sorry, in my haste to get the example out, a couple of typo's where in >> the >> sql. > Next time, don't quote the entire original wrong query... > > Anyway, you probably want to create a trigger on your view and do the > inserts inside the trigger function. > > User created CREATE RULE is not usually the correct answer to a problem. > > David J. Sorry, just noticed the 9.0 so my suggestion is to upgrade :) I'm not sure how best to do this on 9.0 David J. -- View this message in context: http://postgresql.nabble.com/How-to-insert-into-2-tables-from-a-view-tp5831876p5831898.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
On Tue, 2014-12-23 at 15:00 -0500, Chris Hoover wrote: > 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@gmail.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 > > Defining a column as SERIAL will automatically create a sequence. You do not need to supply a value. So:- INSERT INTO table1 (table1_field1) VALUES (in_table1_field1); SELECT lastval() INTO last_row_id; will cause last_row_id to contain the value automatically assigned to column table1_id. Then you can:- INSERT INTO table2 (table1_id, table2_field1) VALUES (last_row_id, in_table2_field1); You could put this into a function returning an integer. If an error occurred then it could return zero, otherwise the value of last_row_id. You need to work out how to handle any errors. Inside the function you can use BEGIN . . WHEN OTHERS . . END; but eventually you have to display the error to your users and you haven't mentioned how the application will do this, or indeed the language being used. I can vaguely remember that in version 9.0 you have to use dollar quoted variables so the first insert would become:- INSERT INTO table1 (table1_field1) VALUES ($1); Rather strange to have two tables sharing the same primary key value. One would have thought it was a one-to-many relationship between table1 and table2. HTH. Rob
Rather strange to have two tables sharing the same primary key value.
One would have thought it was a one-to-many relationship between table1
and table2.
while not particularly common 1-to-1 relationships can be quite useful. Even if not required for the data model and implementation using 1-to-1 makes sense if a subset of the model data has a change profile different than other parts. One of the tables is basically static while the, hopefully smaller (column count) related table has updates performed against it. The main table does not exhibit churn for updates and does not need the related vacuuming.
rob>>I can vaguely remember that in version 9.0 you have to use dollar quoted
rob>>variables so the first insert would become[...]
rob>>variables so the first insert would become[...]
Ppositional variable references are only required for earlier versions of SQL functions; all supported pl/pgsql functions can make use of named arguments.
Note you can also make use of (I think, not sure on the version requirements):
INSERT INTO [...] RETURNING idfield INTO variable
INSERT INTO [...] VALUES (variable);
David J.
View this message in context: Re: How to insert into 2 tables from a view?
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
Chris Hoover wrote:> 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@gmail.com > <mailto:revoohc@gmail.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: > 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 RULE orig_table_insert_rule AS ON INSERT TO orig_table DO INSTEAD ( INSERT INTO table1 (table1_field1) VALUES (NEW.table1_field1); INSERT INTO table2 (table1_id, table2_field1) VALUES (CURRVAL('table1_table1_id_seq'), new.table2_field1); ); COMMIT; INSERT INTO orig_table (table1_field1, table2_field1) VALUES ('The value for table 1, field 1', 'The value for table 2, field1'); SELECT * FROM table1; SELECT * FROM table2; SELECT * FROM orig_table;