From: | Dane Foster <studdugie(at)gmail(dot)com> |
---|---|
To: | Joshua Berkus <josh(at)agliodbs(dot)com> |
Cc: | Don Parris <parrisdc(at)gmail(dot)com>, pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Multi-Table Insert/Update Strategy - Use Functions/Procedures? |
Date: | 2016-01-27 22:30:47 |
Message-ID: | CA+WxinLRagCm7aYUt79bR2BVP12U9B3UvkCZTpOnVEPkeH=KaA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Wed, Jan 27, 2016 at 4:25 PM, Joshua Berkus <josh(at)agliodbs(dot)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(at)postgresql(dot)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
From | Date | Subject | |
---|---|---|---|
Next Message | Hannes Erven | 2016-01-27 22:45:49 | Re: A contradiction in 13.2.1 |
Previous Message | Dane Foster | 2016-01-27 22:23:09 | A contradiction in 13.2.1 |