How to insert into 2 tables from a view?

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:

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.

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

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.

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

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

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

On Tue, Dec 23, 2014 at 6:40 PM, rob stone-2 [via PostgreSQL] <
ml-node+s1045698n5831909h3@n5.nabble.com> 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: http://postgresql.nabble.com/How-to-insert-into-2-tables-from-a-view-tp5831876p5831910.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

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;

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