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.