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 akla...@comcast.net -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
