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.
