X-Original-To: pgsql-general-postgresql.org@localhost.postgresql.org
Received: from localhost (av.hub.org [200.46.204.144])
	by postgresql.org (Postfix) with ESMTP id 2F44A9DCB8E;
	Sun, 11 Dec 2005 14:04:03 -0400 (AST)
Received: from postgresql.org ([200.46.204.71])
	by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024)
	with ESMTP id 04777-10; Sun, 11 Dec 2005 14:04:02 -0400 (AST)
X-Greylist: from auto-whitelisted by SQLgrey-
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from sccrmhc13.comcast.net (sccrmhc13.comcast.net [204.127.202.64])
	by postgresql.org (Postfix) with ESMTP id 935059DCAC0;
	Sun, 11 Dec 2005 14:04:00 -0400 (AST)
Received: from cfgod (pcp08670491pcs.500ash01.tn.comcast.net[69.137.84.227])
	by comcast.net (sccrmhc13) with SMTP
	id <20051211180359013003efu3e>; Sun, 11 Dec 2005 18:03:59 +0000
From: "Foster, Stephen" <stephenlfoster@comcast.net>
To: "'Michael Fuhr'" <mike@fuhr.org>, "'Douglas McNaught'" <doug@mcnaught.org>
Cc: <pgsql-general@postgresql.org>,
	<pgsql-sql@postgresql.org>
Subject: Re: [SQL] Looking for information on PostgreSQL Stored Procedures
Date: Sun, 11 Dec 2005 12:07:26 -0600
Message-ID: <000001c5fe7d$be7f5470$2101a8c0@cfgod>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="windows-1250"
Content-Transfer-Encoding: 7bit
X-Priority: 3 (Normal)
X-MSMail-Priority: Normal
X-Mailer: Microsoft Outlook, Build 10.0.2616
In-Reply-To: <20051211172218.GA87286@winnie.fuhr.org>
Importance: Normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1506
X-Virus-Scanned: by amavisd-new at hub.org
X-Spam-Status: No, score=1.522 required=5 tests=[AWL=-0.797,
	DNS_FROM_RFC_POST=1.44, DNS_FROM_RFC_WHOIS=0.879]
X-Spam-Score: 1.522
X-Spam-Level: *
X-Archive-Number: 200512/538
X-Sequence-Number: 88101

Thanks Michael!  Guess I should have drunk more coffee before replying.
I must have miss that.

-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org] 
Sent: Sunday, December 11, 2005 11:22 AM
To: Douglas McNaught
Cc: Foster, Stephen; pgsql-general@postgresql.org;
pgsql-sql@postgresql.org
Subject: Re: [SQL] [GENERAL] Looking for information on PostgreSQL
Stored Procedures

On Sun, Dec 11, 2005 at 11:38:47AM -0500, Douglas McNaught wrote:
> "Foster, Stephen" <stephenlfoster@comcast.net> writes:
> 
> > WHILE (--Lost on variable name for end of query; EmptyQueryResponse
<>
> > 0? --)
> > 	BEGIN
> > 		IF LastName = fname THEN
> > 			DELETE FROM MailingList WHERE id = id;
> > 		END IF;
> > 		LastName := fname;
> > 		FETCH NEXT FROM NewListCursor INTO fname, id;
> > 	END;
> > CLOSE NewListCursor;
> > $BODY$
> > LANGUAGE 'sql' VOLATILE;
> 
> You can't do any looping or other control structures in an SQL
> function.  Use PL/pgSQL instead.

And as I mentioned in my previous post, you can loop through query
results without messing around with an explicit cursor.

CREATE FUNCTION testfunc() RETURNS void AS $$
DECLARE
    row  record;
BEGIN
    FOR row IN SELECT * FROM tablename ORDER BY whatever LOOP
	-- do stuff that refers to row.column_name
    END LOOP;

    RETURN;
END;
$$ LANGUAGE plpgsql;

-- 
Michael Fuhr

-- 
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date:
12/9/2005
 

-- 
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.13.13/197 - Release Date:
12/9/2005