> is there a way that i can do the foreach using the FOR
> row in SELECT construct while only issuing one select?
I am not sure about what you mean here.
So I am giving a reply to what I think you mean.
the FOR IN can be used like this:
create table blah(
field1 int,
field2 int,
field3 int
);
CREATE FUCNTION blah() RETURN null AS
'
DECLARE
-- declare a variable oif type recrod to store results in
mydata record;
--declare some variables
-- %TYPE allows you to defer to typing of variables
--until exectution time. the type will be whatever the type is for
--the column of the specified table in this case it would be int
-- and that is what all columns of the table are declared as
var1 blah.field1%TYPE;
var2 blah.field2%TYPE;
var3 blah.field3%TYPE;
var4 blah.field3%TYPE;
BEGIN
-- perform query
FOR mydata IN select field1, fields2,field3 from blah where
field3>100 LOOP
--for each row retunred in the query this block will be executed
var1 := mydata.field1;
var2 := mydata.field2;
var3 := mydata.field3;
var4 := var1+var2+var3;
END LOOP;
END;
'language 'plpgslq';
Which says exceute the query "select field1,field2,field3 form blah wehre
field3>100" and for each row returned (the rows get returned n a cursor
called "mydata" - a record is a special type for returned reocrds its
strucutre varies depneidng ofn the data returned from query), assign
field1 to var1, field2 to var2, and field3 to var3 and sum them.
(I dont do anythign with the sum, but that shoudl I hope demonstrate how
the FOR IN LOOP construct can be used).
Thsi url uis the only reference material I ahve found for PL/pgSQL
http://www.postgresql.org/idocs/index.php?plpgsql-description.html
This url has instructions on how to enable PL/pgSQL in postgrers:
http://www.postgresql.org/idocs/index.php?xplang.html
It is not enabled by default.
You will have to do this before you can use any PL/pgSQL.
Hope this helps.
Jason
