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