Re: [SQL] returning composite types.

[Jordan S. Jones]

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.