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

How to insert into 2 tables from a view?

From

Chris Hoover

Date:

23 December 2014, 21:46:45

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

$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

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

From

Chris Hoover

Date:

23 December 2014, 23:00:10

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

$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

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

From

David G Johnston

Date:

23 December 2014, 23:22:28

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.

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

From

David G Johnston

Date:

23 December 2014, 23:25:03

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.

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

From

rob stone

Date:

24 December 2014, 04:39:31



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

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

From

David G Johnston

Date:

24 December 2014, 05:23:38

On Tue, Dec 23, 2014 at 6:40 PM, rob stone-2 [via PostgreSQL] <[hidden email]> wrote:

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[...]

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.

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

From

Berend Tober

Date:

24 December 2014, 05:41:22

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;