Re: How to insert into 2 tables from a view? - Mailing list pgsql-general
From | Berend Tober |
---|---|
Subject | Re: How to insert into 2 tables from a view? |
Date | |
Msg-id | 549A27C6.7030208@computer.org Whole thread Raw |
In response to | Re: How to insert into 2 tables from a view? (Chris Hoover <revoohc@gmail.com>) |
List | pgsql-general |
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;
pgsql-general by date: