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

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