Обсуждение: Rules on Select

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?

Thanks

Sean,

I am looking to do the same thing...

I have so far posted with little relevant response to pgsql-admin,
pgsql-general and pgsql-sql

If you get any help please forward my way...

I will do the same..

What I can tell you is that it is possible to write the Select
Statements submitted to the Postgres Log Files...

The only way I can think of to accomplish our task would be to set
Logging to ALL and then parse the Log Files into a Table...

This is not an option I wish to pursue though it may work for you.

Jim

See my original post to this list :
From: "Hogan, James F. Jr." <JHogan ( at ) seton ( dot ) org>
To: <pgsql-sql ( at ) postgresql ( dot ) org>, <pgsql-general ( at )
postgresql ( dot ) org>
Subject: audit table containing Select statements submitted
Date: Thu, 4 May 2006 12:45:59 -0500


-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sean Hamilton
Sent: Friday, May 05, 2006 3:48 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Rules on Select

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?

Thanks

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

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

This is best done by using a trigger on the table.
http://www.varlena.com/GeneralBits/38.php shows how
to do it with both a trigger and with a rule.

--elein
elein@varlena.com

On Fri, May 05, 2006 at 04:48:28PM -0400, Sean Hamilton 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?
>
> Thanks
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: if posting/reading through Usenet, please send an appropriate
>       subscribe-nomail command to majordomo@postgresql.org so that your
>       message can get through to the mailing list cleanly
>