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