Message-ID: <3E85E50A.4040107@joeconway.com>
Date: Sat, 29 Mar 2003 10:25:14 -0800
From: Joe Conway <mail@joeconway.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US;
	rv:1.2.1) Gecko/20021130
MIME-Version: 1.0
To: Franco Bruno Borghesi <franco@akyasociados.com.ar>
Cc: list@racistnames.com, pgsql-sql@postgresql.org
Subject: Re: returning composite types.
References: <200303282020.25997.franco@akyasociados.com.ar>
	<200303291320.05734.franco@akyasociados.com.ar>
	<3E85CE8B.3010700@joeconway.com>
	<200303291435.04727.franco@akyasociados.com.ar>
In-Reply-To: <200303291435.04727.franco@akyasociados.com.ar>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

Franco Bruno Borghesi wrote:
> yes, i was trying to do something like that, but it breaks always in the same 
> place, first I thought that it was because of the way I was assigning values 
> to the fields of my row, but now I'm beginning to think that the reason is 
> the way I pass the row to f2. 
> 
> Here is the error:
> franco=# SELECT f1();
> WARNING:  Error occurred while executing PL/pgSQL function f1
> WARNING:  line 5 at select into variables
> ERROR:  Attribute "result" not found
> 
> CREATE TYPE mytype AS (val1 INTEGER, val2 INTEGER, val3 INTEGER);
> 
> CREATE OR REPLACE FUNCTION f1() RETURNS mytype AS '
> DECLARE   
>    result mytype%ROWTYPE;
> BEGIN
>    result.val1:=1;   
>    SELECT val2, val3 INTO result.val2, result.val3 FROM f2(result);

It looks like plpgsql doesn't support composite type variables as 
arguments to functions that are called from within a function. The error 
is saying it cannot find an attribute named result -- that's because 
there is no *attribute* called result, there is a rowtype variable.

I'm not sure right off what is involved in fixing this, but you can 
always pass the individual attributes to f2:

CREATE TYPE mytype AS (val1 INTEGER, val2 INTEGER, val3 INTEGER);

CREATE OR REPLACE FUNCTION f2(int,int,int) RETURNS mytype AS '
DECLARE
    arg1 ALIAS FOR $1;
    arg2 ALIAS FOR $2;
    arg3 ALIAS FOR $3;
    newval2 int;
    newval3 int;
    result mytype%ROWTYPE;
BEGIN
    newval2 := coalesce(arg2,2) * arg1;
    newval3 := coalesce(arg3,3) * arg1;
    SELECT INTO result.val1, result.val2, result.val3
      arg1, newval2, newval3;
    RETURN result;
END;
' LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION f1(int) RETURNS mytype AS '
DECLARE
    result mytype%ROWTYPE;
BEGIN
    result.val1:=$1;
    SELECT INTO result.val2, result.val3 val2, val3
      FROM f2(result.val1, result.val2, result.val3);
    RETURN result;
END;
' LANGUAGE 'plpgsql';

regression=# select * from f1(2);
  val1 | val2 | val3
------+------+------
     2 |    4 |    6
(1 row)

HTH,

Joe