Multi-Table Insert/Update Strategy - Use Functions/Procedures?

On this track (possibly unrelated)...can a view be used as part of the
multi table update
Just curious

On Wed, Jan 27, 2016 at 1:28 PM, Bret Stern <
bret_stern@machinemanagement.com> wrote:

On this track (possibly unrelated)...can a view be used as part of the
multi table update
Just curious

​Never done so myself but - I believe so though doing so over a one-to-many
relationship can be tricky.

That said probably no trickier than do so via a function. In both
scenarios you need to use arrays to approximate sets of records and then
decompose the array to insert the relevant records in the "many" table.

David J.

Am I on the right track, or is there some better way to set this up? My
understanding is that views really aren't meant for insert/update
operations, and I have seen on the web that using views to insert/update is
a bit tricky - and still requires a procedure with a rule on the view.

Why not use updatable CTEs? That's what they're for.

WITH update_contact as (
INSERT INTO contacts ( contact_id, name )
VALUES ( nexval('contacts_id_seq'), 'Joe' )
RETURNING contact_id ),
new_cont_ids AS (
SELECT contact_id FROM update_contact;
),
insert_phones AS (
INSERT INTO phones ( phone_id, contact_id, phone_no )
SELECT ( nextval('phone_id_seq'), contact_id, '415-555-1212' )
FROM new_cont_ids
RETURNING phone_id
) ...

I think you get the idea. On 9.3 or later, this is the way to go.

--
Josh Berkus
Red Hat OSAS
(opinions are my own)

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

On Wed, Jan 27, 2016 at 4:25 PM, Joshua Berkus <josh@agliodbs.com> wrote:

Am I on the right track, or is there some better way to set this up? My
understanding is that views really aren't meant for insert/update
operations, and I have seen on the web that using views to insert/update

is

a bit tricky - and still requires a procedure with a rule on the view.

Why not use updatable CTEs? That's what they're for.

WITH update_contact as (
INSERT INTO contacts ( contact_id, name )
VALUES ( nexval('contacts_id_seq'), 'Joe' )
RETURNING contact_id ),
new_cont_ids AS (
SELECT contact_id FROM update_contact;
),
insert_phones AS (
INSERT INTO phones ( phone_id, contact_id, phone_no )
SELECT ( nextval('phone_id_seq'), contact_id, '415-555-1212' )
FROM new_cont_ids
RETURNING phone_id
) ...

I think you get the idea. On 9.3 or later, this is the way to go.

--
Josh Berkus
Red Hat OSAS
(opinions are my own)

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

​
In general do updateable CTEs have lower overhead than functions?

Dane
​

On Wed, Jan 27, 2016 at 4:25 PM, Joshua Berkus <josh@agliodbs.com> wrote:

Am I on the right track, or is there some better way to set this up? My
understanding is that views really aren't meant for insert/update
operations, and I have seen on the web that using views to insert/update

is

a bit tricky - and still requires a procedure with a rule on the view.

Why not use updatable CTEs? That's what they're for.

Sounds great. But can I use variables, and allow the db user to enter the
data when the CTE is called? I've used variables in Python scripts for
insert/update/delete, but honestly, I've never used a variable in my
queries in PostgreSQL. So, instead of 'Joe', as in your example below,
maybe something like first_name?

On Wed, Jan 27, 2016 at 4:03 PM, Don Parris <parrisdc@gmail.com> wrote:

On Wed, Jan 27, 2016 at 4:25 PM, Joshua Berkus <josh@agliodbs.com> wrote:

Am I on the right track, or is there some better way to set this up? My
understanding is that views really aren't meant for insert/update
operations, and I have seen on the web that using views to

insert/update is

a bit tricky - and still requires a procedure with a rule on the view.

Why not use updatable CTEs? That's what they're for.

Sounds great. But can I use variables, and allow the db user to enter the
data when the CTE is called? I've used variables in Python scripts for
insert/update/delete, but honestly, I've never used a variable in my
queries in PostgreSQL. So, instead of 'Joe', as in your example below,
maybe something like first_name?

WITH update_contact as (
INSERT INTO contacts ( contact_id, name )
VALUES ( nexval('contacts_id_seq'), 'Joe' )
RETURNING contact_id ),
new_cont_ids AS (
SELECT contact_id FROM update_contact;
),
insert_phones AS (
INSERT INTO phones ( phone_id, contact_id, phone_no )
SELECT ( nextval('phone_id_seq'), contact_id, '415-555-1212' )
FROM new_cont_ids
RETURNING phone_id
) ...

I think you get the idea. On 9.3 or later, this is the way to go.

​Parameter passing and variables are client-side considerations. You
haven't told us how you plan to execute the SQL.

​IMO the most straight-forward API is a function. Whether you implement
that function using a updating CTE or a sequence of separate SQL commands
is up to you to decide and, if performance matters, benchmark.

Comparing a CTE and function in general doesn't really do much good. There
are many non-performance concerns involved and the specific usage pattern
involved will matter greatly in determining overhead.

David J.

On Wed, Jan 27, 2016 at 6:24 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Wed, Jan 27, 2016 at 4:03 PM, Don Parris <parrisdc@gmail.com> wrote:

<snip>

​Parameter passing and variables are client-side considerations. You
haven't told us how you plan to execute the SQL.

​IMO the most straight-forward API is a function. Whether you implement
that function using a updating CTE or a sequence of separate SQL commands
is up to you to decide and, if performance matters, benchmark.

Comparing a CTE and function in general doesn't really do much good.
There are many non-performance concerns involved and the specific usage
pattern involved will matter greatly in determining overhead.

Thanks David, that makes sense. My main front-end - at the moment - is
LibreOffice Base. With Base, I can probably just create forms using the
underlying tables. That said, I may also want to write a Python front-end,
in which case I would prefer to let the database do the work it was
designed to do.

Don
--
D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate
http://dcparris.net/ <https://www.xing.com/profile/Don_Parris&gt;
<http://www.linkedin.com/in/dcparris&gt;
GPG Key ID: F5E179BE

Don Parris wrote:

I have several tables...
and want db users to be able to add or update ...
... in one step, and get all the information
into the correct tables.

I think I am ok with setting the privileges on the tables and columns as
appropriate to allow each group to select, insert and update the
appropriate data, and I can create appropriate views for them ...

Ideally, the db user just says "I want to enter Joe Public, and Joe is
affiliated with the Widget Corp entity, and has the phone numbers..."

Am I on the right track, or is there some better way to set this up? My
understanding is that views really aren't meant for insert/update
operations, and I have seen on the web that using views to insert/update
is a bit tricky - and still requires a procedure with a rule on the view.

The way I do it for the insert case is to define an INSTEAD OF INSERT
trigger on the view:

CREATE OR REPLACE VIEW protected.bond_ask AS
SELECT ...
FROM private.bond_ask
JOIN private.order_book ON ...
;

CREATE OR REPLACE FUNCTION protected.bond_ask_iit()
RETURNS trigger AS
$BODY$
BEGIN
...

INSERT INTO private.order_book (...)
VALUES (...) RETURNING order_book_id INTO new.order_book_id;

INSERT INTO private.bond_ask (...)
VALUES (...)
RETURNING bond_id into new.bond_id;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

CREATE TRIGGER bond_ask_iit
INSTEAD OF INSERT
ON protected.bond_ask
FOR EACH ROW
EXECUTE PROCEDURE protected.bond_ask_iit();

And then grant insert privilege on the view.

You can probably do something similar for updates.

--B

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