From: "Rudi Starcevic" <rudi@oasis.net.au>
To: pgsql-sql@postgresql.org
Subject: Re: Logging select statements
Date: Wed, 9 Jul 2003 20:32:32 +1000
Message-Id: <20030709103232.M72964@oasis.net.au>
In-Reply-To: <9424A5E8-B1F3-11D7-BB21-000393B3A702@canprint.com.au>
References: <3F0BBC7D.7060303@oasis.net.au>
	<9424A5E8-B1F3-11D7-BB21-000393B3A702@canprint.com.au>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1

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.