From: Adrian Klaver <aklaver@comcast.net>
To: peterw@borstad.com
Cc: pgsql-sql@postgresql.org
Subject: Re: FUNCTION problem
Date: Fri, 3 Apr 2009 16:59:16 +0000 (UTC)
Message-ID: <1381545833.2939121238777956737.JavaMail.root@sz0030a.emeryville.ca.mail.comcast.net> (raw)
In-Reply-To: <49D62B3F.9030007@borstad.com>
----- "Peter Willis" <peterw@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
> >>> aklaver@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
> >> aklaver@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
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: <1381545833.2939121238777956737.JavaMail.root@sz0030a.emeryville.ca.mail.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