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

> 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