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: (view raw, whole thread or download thread mbox)
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

	-- 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;

	-- 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;
'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

This url has instructions on how to enable PL/pgSQL in postgrers:

It is not enabled by default.

You will have to do this before you can use any PL/pgSQL.

Hope this helps.

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-2017 The PostgreSQL Global Development Group