From: Peter Willis <peterw@borstad.com>
To: Adrian Klaver <aklaver@comcast.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: FUNCTION problem
Date: Fri, 03 Apr 2009 08:29:03 -0700
Message-ID: <49D62B3F.9030007@borstad.com> (raw)
In-Reply-To: <200904030712.38321.aklaver@comcast.net>
References: <1521645855.2691471238710811603.JavaMail.root@sz0030a.emeryville.ca.mail.comcast.net>
<200904021816.44804.aklaver@comcast.net>
<200904030651.05480.aklaver@comcast.net>
<200904030712.38321.aklaver@comcast.net>
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.
Thank you very much for your help.
Peter
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: peterw@borstad.com, aklaver@comcast.net
Subject: Re: FUNCTION problem
In-Reply-To: <49D62B3F.9030007@borstad.com>
* 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