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

[Berend Tober <btober@xxxxxxxxxxxxxxx>]

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@xxxxxxxxx
> <mailto:revoohc@xxxxxxxxx>> 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;



--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general