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