From: | Joe Conway <mail(at)joeconway(dot)com> |
---|---|
To: | Franco Bruno Borghesi <franco(at)akyasociados(dot)com(dot)ar> |
Cc: | list(at)racistnames(dot)com, pgsql-sql(at)postgresql(dot)org |
Subject: | Re: returning composite types. |
Date: | 2003-03-29 18:25:14 |
Message-ID: | 3E85E50A.4040107@joeconway.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Franco Bruno Borghesi | 2003-03-29 18:56:26 | Re: returning composite types. |
Previous Message | Franco Bruno Borghesi | 2003-03-29 17:35:04 | Re: returning composite types. |