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> (raw)
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>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: rudi@oasis.net.au
Subject: Re: Logging select statements
In-Reply-To: <20030709103232.M72964@oasis.net.au>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox