Received: from localhost (unknown [200.46.204.183])
	by mail.postgresql.org (Postfix) with ESMTP id 517F8635572
	for <pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu,  2 Apr 2009 22:16:48 -0300 (ADT)
Received: from mail.postgresql.org ([200.46.204.86])
	by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024)
	with ESMTP id 38450-04 for
	<pgsql-sql-postgresql.org@mail.postgresql.org>;
	Thu,  2 Apr 2009 22:16:46 -0300 (ADT)
X-Greylist: from auto-whitelisted by SQLgrey-1.7.6
Received: from QMTA15.emeryville.ca.mail.comcast.net
	(qmta15.emeryville.ca.mail.comcast.net [76.96.27.228])
	by mail.postgresql.org (Postfix) with ESMTP id A9175634CD3
	for <pgsql-sql@postgresql.org>; Thu,  2 Apr 2009 22:16:46 -0300 (ADT)
Received: from OMTA06.emeryville.ca.mail.comcast.net ([76.96.30.51])
	by QMTA15.emeryville.ca.mail.comcast.net with comcast
	id adw81b00316AWCUAFpGl6U; Fri, 03 Apr 2009 01:16:45 +0000
Received: from tucker.local ([24.17.164.54])
	by OMTA06.emeryville.ca.mail.comcast.net with comcast
	id apGk1b00e1Ak4z48SpGl5E; Fri, 03 Apr 2009 01:16:45 +0000
From: Adrian Klaver <aklaver@comcast.net>
To: peterw@borstad.com
Subject: Re: FUNCTION problem
Date: Thu, 2 Apr 2009 18:16:44 -0700
User-Agent: KMail/1.9.10
Cc: pgsql-sql@postgresql.org
References: 
 <1521645855.2691471238710811603.JavaMail.root@sz0030a.emeryville.ca.mail.comcast.net>
	<49D5489E.5010904@borstad.com>
In-Reply-To: <49D5489E.5010904@borstad.com>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200904021816.44804.aklaver@comcast.net>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none
X-Spam-Level: 
X-Archive-Number: 200904/25
X-Sequence-Number: 32371

On Thursday 02 April 2009 4:22:06 pm Peter Willis wrote:
> Adrian Klaver wrote:
> > Did you happen to catch this:
> > Note that functions using RETURN NEXT or RETURN QUERY must be called as a
> > table source in a FROM clause
> >
> > Try:
> > select * from test_function(1)
>
> I did miss that, but using that method to query the function
> didn't work either. Postgres doesn't see the result as a
> tabular set of records.
>
> Even if I replace the FOR loop with:
>
> <quote>
> FOR R IN SELECT * FROM pg_database LOOP
> 	RETURN NEXT R;
> END LOOP;
>
> </quote>
>
> I get the same error(s). I don't think postgres likes
> the unrelated 'SELECT INTO <variable> [column] FROM [QUERY] LIMIT 1'
> lines before the FOR loop...
>
> I think I need to go back and approach the function from a
> different direction.
>
> Thanks for all the pointers.
>
> Peter

Now I remember. Its something that trips me up, the RECORD in RETURN setof 
RECORD is not the same thing as the RECORD in DECLARE RECORD. See below for a 
better explanation-
http://www.postgresql.org/docs/8.3/interactive/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS
Note that RECORD is not a true data type, only a placeholder. One should also 
realize that when a PL/pgSQL function is declared to return type record, this 
is not quite the same concept as a record variable, even though such a function 
might use a record variable to hold its result. In both cases the actual row 
structure is unknown when the function is written, but for a function returning 
record the actual structure is determined when the calling query is parsed, 
whereas a record variable can change its row structure on-the-fly.



-- 
Adrian Klaver
aklaver@comcast.net