Re: returning composite types.

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 * INTO result 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.