From: Adrian Klaver <aklaver@comcast.net>
To: pgsql-sql@postgresql.org
Subject: Re: FUNCTION problem
Date: Fri, 3 Apr 2009 07:12:38 -0700
User-Agent: KMail/1.9.10
Cc: peterw@borstad.com
References: 
 <1521645855.2691471238710811603.JavaMail.root@sz0030a.emeryville.ca.mail.comcast.net>
	<200904021816.44804.aklaver@comcast.net>
	<200904030651.05480.aklaver@comcast.net>
In-Reply-To: <200904030651.05480.aklaver@comcast.net>
MIME-Version: 1.0
Content-Type: text/plain;
  charset="utf-8"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline
Message-Id: <200904030712.38321.aklaver@comcast.net>

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)


-- 
Adrian Klaver
aklaver@comcast.net