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

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;