From: Adrian Klaver <aklaver@comcast.net>
To: peterw@borstad.com
Cc: pgsql-sql@postgresql.org
Subject: Re: FUNCTION problem
Date: Thu, 2 Apr 2009 18:16:44 -0700
Message-ID: <200904021816.44804.aklaver@comcast.net> (raw)
In-Reply-To: <49D5489E.5010904@borstad.com>
References: <1521645855.2691471238710811603.JavaMail.root@sz0030a.emeryville.ca.mail.comcast.net>
<49D5489E.5010904@borstad.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: aklaver@comcast.net, peterw@borstad.com
Subject: Re: FUNCTION problem
In-Reply-To: <200904021816.44804.aklaver@comcast.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox