On 5/5/06, Sean Hamilton <[EMAIL PROTECTED]> 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;
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
