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

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