Re: FUNCTION problem

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(at)comcast(dot)net