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.
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
