On 5/5/06, Sean Hamilton <sehamilt@xxxxxxxxxx> 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;
