Thread: returning composite types.

Hi guys.

I'm working with functions in my database, using plpgsql, but I reached a
point where I realize I'm missing a concept: how do I return composite types
from a function? I'll give you an example:

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

If I want my function to return a "mytype" type, should I declare it as:
CREATE FUNCTION myFunction() RETURNS mytype AS ...
or maybe
CREATE FUNCTION myFunction() RETURNS SETOF mytype AS ...

and in any case, inside the function, how should I declare the variable
holding the return value?:

DECLARE  result mytype;
BEGIN
...  RETURN result;
END;

or maybe

DECLARE  result mytype%ROWTYPE;
BEGIN
...  RETURN result;
END;

I've read the documentation and the examples in it, but I still don't
understand what the right way is. If you could give an example of a function
filling "mytipe" and returning it, it would really help me.

Thanks in advance.

Here is how I have been doing mine:<br /><br /><blockquote><tt>CREATE FUNCTION "myFunction" () RETURNS SETOF mytype<br
/>AS<br /> '<br /> DECLARE<br />     r      mytype%ROWTYPE;<br /> BEGIN<br />     FOR r IN [SELECT STATEMENT]<br />    
LOOP<br/>        RETURN NEXT r;<br />     END LOOP;<br />     RETURN;<br /> END;<br /> '<br /> LANGUAGE 'plpgsql';<br
/></tt></blockquote>Hope this Helps..<br /><br /> Jordan S. Jones<br /><br /><br /> Franco Bruno Borghesi wrote:<br
/><blockquotecite="mid200303282020.25997.franco@akyasociados.com.ar" type="cite"><pre wrap="">Hi guys.
 

I'm working with functions in my database, using plpgsql, but I reached a 
point where I realize I'm missing a concept: how do I return composite types 
from a function? I'll give you an example:

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

If I want my function to return a "mytype" type, should I declare it as:
CREATE FUNCTION myFunction() RETURNS mytype AS ...
or maybe
CREATE FUNCTION myFunction() RETURNS SETOF mytype AS ...

and in any case, inside the function, how should I declare the variable 
holding the return value?:

DECLARE  result mytype;
BEGIN
...  RETURN result;   
END;   

or maybe      

DECLARE  result mytype%ROWTYPE;
BEGIN
...  RETURN result;   
END;   

I've read the documentation and the examples in it, but I still don't 
understand what the right way is. If you could give an example of a function 
filling "mytipe" and returning it, it would really help me.

Thanks in advance.  </pre></blockquote>

ok, soy you're telling me that the only way to return a composite type is
using a set of them, even if I know my function will allways return 1 record.

And tell me: can I assign values to this record without using the "for" as you
did? maybe a SELECT INTO, or something.

I'll tell you what I'm trying to do, maybe that will help: I have 4 functions,
that do specific tasks, and they are called in a chained mode. I want the
first function in the chain to call the other functions, and return all the
results in a composite type, like this:

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

CREATE FUNCTION f1() RETURNS SETOF mytype AS '
DECLARE  result mytype%ROWTYPE;
BEGIN  result.val1=/*something*/;  SELECT * INTO result FROM f2(result);     RETURN NEXT result;  RETURN;
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION f2(mytype) RETURNS SETOF mytype AS '
DECLARE  result ALIAS FOR $1;
BEGIN  result.val2=2;  IF (result.val1>50) THEN     SELECT * INTO result FROM f3(result);           ELSE     SELECT *
INTOresult FROM f4(result);  END IF;        RETURN NEXT result;  RETURN; 
END;
' LANGUAGE 'plpgsql';

CREATE FUNCTION f3(mytype) RETURNS SETOF mytype AS '
DECLARE  result ALIAS FOR $1;
BEGIN  /*do something with result*/  RETURN NEXT result;  RETURN;
END;
' LANGUAGE 'plpgsql';
CREATE FUNCTION f4(mytype) RETURNS SETOF mytype AS '
DECLARE  result ALIAS FOR $1;
BEGIN  /*do something with result*/  RETURN NEXT result;  RETURN;
END;
' LANGUAGE 'plpgsql';

In a programming language I would pass mytype by reference, and I was trying
to something like that here in plpgsql.


On Friday 28 March 2003 21:32, Jordan S. Jones wrote:
> Here is how I have been doing mine:
>
>     CREATE FUNCTION "myFunction" () RETURNS SETOF mytype
>     AS
>     '
>     DECLARE
>         r      mytype%ROWTYPE;
>     BEGIN
>         FOR r IN [SELECT STATEMENT]
>         LOOP
>            RETURN NEXT r;
>         END LOOP;
>         RETURN;
>     END;
>     '
>     LANGUAGE 'plpgsql';
>
> Hope this Helps..
>
> Jordan S. Jones
>
> Franco Bruno Borghesi wrote:
> >Hi guys.
> >
> >I'm working with functions in my database, using plpgsql, but I reached a
> >point where I realize I'm missing a concept: how do I return composite
> > types from a function? I'll give you an example:
> >
> >CREATE TYPE mytype AS(
> >   val1 INTEGER,
> >   val2 INTEGER,
> >   val3 INTEGER,
> >   val4 INTEGER
> >);
> >
> >If I want my function to return a "mytype" type, should I declare it as:
> >CREATE FUNCTION myFunction() RETURNS mytype AS ...
> >or maybe
> >CREATE FUNCTION myFunction() RETURNS SETOF mytype AS ...
> >
> >and in any case, inside the function, how should I declare the variable
> >holding the return value?:
> >
> >DECLARE
> >   result mytype;
> >BEGIN
> >...
> >   RETURN result;
> >END;
> >
> >or maybe
> >
> >DECLARE
> >   result mytype%ROWTYPE;
> >BEGIN
> >...
> >   RETURN result;
> >END;
> >
> >I've read the documentation and the examples in it, but I still don't
> >understand what the right way is. If you could give an example of a
> > function filling "mytipe" and returning it, it would really help me.
> >
> >Thanks in advance.

Franco Bruno Borghesi wrote:
> ok, soy you're telling me that the only way to return a composite type is 
> using a set of them, even if I know my function will allways return 1 record.

Try this:

create type foo as (f1 int, f2 text);
create or replace function retfoo(int, text) returns foo as ' declare  result foo%ROWTYPE; begin  select into result
$1,$2;  return result; end;
 
' language 'plpgsql';

regression=# select * from retfoo(2,'b'); f1 | f2
----+----  2 | b
(1 row)

Joe

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);  RETURN result;
END;
' LANGUAGE 'plpgsql';

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

what do you guys think?

On Saturday 29 March 2003 13:49, Joe Conway wrote:
> Franco Bruno Borghesi wrote:
> > ok, soy you're telling me that the only way to return a composite type is
> > using a set of them, even if I know my function will allways return 1
> > record.
>
> Try this:
>
> create type foo as (f1 int, f2 text);
> create or replace function retfoo(int, text) returns foo as '
>   declare
>    result foo%ROWTYPE;
>   begin
>    select into result $1, $2;
>    return result;
>   end;
> ' language 'plpgsql';
>
> regression=# select * from retfoo(2,'b');
>   f1 | f2
> ----+----
>    2 | b
> (1 row)
>
> Joe

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

so what you're saying is that it's not a syntactical error, it's just an
unsupported feature.

Ok then, I will just call my functions sending the values from my composite
type individually.

thanks guys!

On Saturday 29 March 2003 15:25, Joe Conway wrote:
> 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