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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
