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