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>