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

On Wed, Jan 27, 2016 at 4:03 PM, Don Parris <parrisdc(at)gmail(dot)com> wrote:

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