On Wed, Jan 27, 2016 at 4:25 PM, Joshua Berkus <josh@xxxxxxxxxxxx> 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.
