From: | "Tony Wasson" <ajwasson(at)gmail(dot)com> |
---|---|
To: | "Sean Hamilton" <sehamilt(at)chfund(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Rules on Select |
Date: | 2006-05-05 22:07:41 |
Message-ID: | 6d8daee30605051507k1551342ep4979f2b07b064e02@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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;
From | Date | Subject | |
---|---|---|---|
Next Message | Oscar Arca | 2006-05-05 22:08:51 | recuperar el nro del registro actualizado utilizando Ado |
Previous Message | Tzahi Fadida | 2006-05-05 21:57:46 | Google Summer of Code: Full Disjunctions |