Re: Rules on Select

On 5/5/06, Sean Hamilton <sehamilt(at)chfund(dot)org> wrote:
> I have to log each time a user selects data from a table by inserting
> record in another table.
> For example
> I have table xzy
> user test selects id, name from table xyz.
> I want to insert into table xyz_log
> user, action, fields, timestamp
> test, select, id name, 12/1/05 02:00:21
>
> How can i do this using a Rule or Trigger?

For a small table you could use a SQL set returning function like so.
You can get more granular if you require an argument to the function,
like a certain id or name.

CREATE TABLE log (pguser TEXT, action TEXT, fields TEXT, timestamp
TIMESTAMP DEFAULT NOW());

CREATE TABLE xyz (id INT, name TEXT);

CREATE OR REPLACE FUNCTION xyz() RETURNS SETOF xyz AS
$BODY$
INSERT INTO log (pguser, action, fields) VALUES (CURRENT_USER,'select','all');
SELECT id, name FROM xyz;
$BODY$ language sql;