Обсуждение: Multi-Table Insert/Update Strategy - Use Functions/Procedures?

I have several tables related to people and their contact information, and want db users to be able to add or update a given person and their respective contact information in one step, and get all the information into the correct tables.

I think I am ok with setting the privileges on the tables and columns as appropriate to allow each group to select, insert and update the appropriate data, and I can create appropriate views for them to view data they need to see. However, I am not really sure about the best way to allow someone to actually insert/update the data.

For instance, given the following tables:

core.category
contact.entity

contact.person

contact.entity_category --linking table between entity and category

contact.person_entity --linking table between entity & person

... --other tables for address and phone contact info

I haven't really given much thought as to how such a procedure might look, but I'm guessing something along the lines of:
CREATE FUNCTION record_insert() RETURNS integer AS $$

BEGIN
INSERT statements... --need PK from entity & category tables to insert into entity_category table.

END

$$

language plpgsql;

Ideally, the db user just says "I want to enter Joe Public, and Joe is affiliated with the Widget Corp entity, and has the phone numbers..."

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.

Thanks,

Don

--

D.C. Parris, FMP, Linux+, ESL Certificate
Minister, Security/FM Coordinator, Free Software Advocate

http://dcparris.net/

GPG Key ID: F5E179BE

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

Don Parris wrote:
> I have several tables...
> and want db users to be able to add or update ...
> ... in one step, and get all the information
> into the correct tables.
>
> I think I am ok with setting the privileges on the tables and columns as
> appropriate to allow each group to select, insert and update the
> appropriate data, and I can create appropriate views for them ...
>
> Ideally, the db user just says "I want to enter Joe Public, and Joe is
> affiliated with the Widget Corp entity, and has the phone numbers..."
>
> 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.

The way I do it for the insert case is to define an INSTEAD OF INSERT
trigger on the view:

CREATE OR REPLACE VIEW protected.bond_ask AS
  SELECT ...
    FROM private.bond_ask
    JOIN private.order_book ON ...
  ;

CREATE OR REPLACE FUNCTION protected.bond_ask_iit()
   RETURNS trigger AS
$BODY$
BEGIN
    ...

   INSERT INTO private.order_book (...)
     VALUES (...) RETURNING order_book_id INTO new.order_book_id;

   INSERT INTO private.bond_ask (...)
     VALUES (...)
     RETURNING bond_id into new.bond_id;
   RETURN NEW;
END;
$BODY$
   LANGUAGE plpgsql VOLATILE SECURITY DEFINER;

CREATE TRIGGER bond_ask_iit
   INSTEAD OF INSERT
   ON protected.bond_ask
   FOR EACH ROW
   EXECUTE PROCEDURE protected.bond_ask_iit();

And then grant insert privilege on the view.

You can probably do something similar for updates.

--B