Skip site navigation (1) Skip section navigation (2)

Re: the best way? (maybe a PL/pgSQL question)

From: Jason Tan <jason(at)rebel(dot)rebel(dot)net(dot)au>
To: "Robert J(dot) Sanford, Jr(dot)" <rsanford(at)nolimitsystems(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: the best way? (maybe a PL/pgSQL question)
Date: 2001-09-04 04:40:38
Message-ID: Pine.LNX.3.95.1010904135435.26091G-100000@rebel.rebel.net.au (view raw or flat)
Thread:
Lists: pgsql-novice

> 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


In response to

pgsql-novice by date

Next:From: Chuck WolberDate: 2001-09-04 04:45:17
Subject: Re: Perl DBI Drivers
Previous:From: postgresDate: 2001-09-04 04:40:35
Subject: Re: Perl DBI Drivers

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group