----- "Peter Willis" <pet...@borstad.com> wrote: > Adrian Klaver wrote: > > On Friday 03 April 2009 6:51:05 am Adrian Klaver wrote: > >> On Thursday 02 April 2009 6:16:44 pm Adrian Klaver wrote: > >>> 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# > >>> PL PGSQL-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 > >> For this particular case the following works. > >> > >> CREATE OR REPLACE FUNCTION test_function(integer) RETURNS record > >> AS $Body$ > >> DECLARE croid integer; > >> DECLARE R RECORD; > >> BEGIN > >> SELECT INTO croid 2; > >> SELECT INTO R croid,$1; > >> RETURN R; > >> END; > >> > >> $Body$ > >> LANGUAGE plpgsql; > >> > >> -- > >> Adrian Klaver > >> akla...@comcast.net > > > > Forgot to show how to call it. > > > > test=# SELECT * from test_function(1) as test(c1 int,c2 int); > > c1 | c2 > > ----+---- > > 2 | 1 > > (1 row) > > > > > > Ah!, I see what you mean about the definition of 'RECORD'. > (The lights come on...) > > And here I thought it would all be so simple..... > > You show a valid, and most informative solution. > This should get things working for me.
If you are using Postgres 8.1+ then it becomes even easier because you can use OUT parameters in the function argument list to eliminate the "as test(c1 int,c2 int)" clause. At this point it becomes a A-->B-->C problem i.e determine what your inputs are, how you want to process them and how you want to return the output. > > Thank you very much for your help. > > Peter 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
