Re: Logging select statements

Matthew,

Gee thanks ..

I just read over Stephan's Set Returning Function last night ..
I was trying to see how I could use it.

> Hope that is what you were after!

Indeed it is. Your 'rough and ready solution' solution is a
mighty fine place to begin.

Thanks aplenty to you and Achilleus for taking the time to
look at this for me - another reason why I love PostgreSQL ! :-)

Best regards
Rudi.

> 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.