Re: Logging select statements

Hi Rudi,

You can't trigger on a SELECT, but you could wrap your SQL in a set
returning function...

http://techdocs.postgresql.org/guides/SetReturningFunctions

Here is a rough and ready solution:

CREATE TABLE access_log ( id int not null );

CREATE TABLE datatable (
id int not null primary key,
somedata varchar(255) not null
);

INSERT INTO datatable VALUES( 1, 'apple' );
INSERT INTO datatable VALUES( 2, 'orange' );
INSERT INTO datatable VALUES( 3, 'banana' );

CREATE OR REPLACE FUNCTION get_rows_and_log( varchar ) RETURNS SETOF
record AS
'
DECLARE
r record;
BEGIN
FOR r IN EXECUTE ''SELECT * FROM '' || $1 LOOP
INSERT INTO access_log VALUES( r.id );
RETURN NEXT r;
END LOOP;
RETURN;
END;
'
LANGUAGE 'plpgsql';

Now, as an example, do:

SELECT * FROM get_rows_and_log( 'datatable' ) AS data( id int, somedata
varchar);

You'll get the data returned, and the log entries will be made.

You can put your WHERE clause in the parameter:

SELECT * FROM get_rows_and_log( 'datatable WHERE somedata LIKE ''%e'''
) AS data( id int, somedata varchar);

Hope that is what you were after!

Cheers

Matthew.

On Wednesday, July 9, 2003, at 04:55 PM, Rudi Starcevic wrote:

> Thanks Achilleus,
>
> I know there's a couple of ways I could do this.
>
> In my first email I can see a senario of 1 select plus 100 inserts.
>
> Another may be 1 select plus 1 insert.
> For example;
> In a table of 3000 rows a user submits a query which returns 100 rows.
> I could loop through the result set and build a string of id's (
> 1,2,5,7,8,9,44,22 etc ) and
> make one insert into a logging table of the entire string.