Re: Variable return type...

Thanks a lot, your answer enlighten me a lot in the path to take to resolve
the problem into the database...

Thanks man...

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Michael Fuhr
Sent: Miércoles, 26 de Octubre de 2005 07:38 p.m.
To: Cristian Prieto
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Variable return type...

On Wed, Oct 26, 2005 at 06:14:23PM -0600, Cristian Prieto wrote:
> Hi, I was working in a set of SPs inside PL/pgSQL and I was wonder if I
> could return a set of variable types from a function, for example, in some
> time the same function could return a set of tuples with an integer and a
> string, in other times It may return a set of tuples with an integer, a
> string and another string. a so on.

You could declare the function to return a record type.  You'll
have to provide a column definition list when you call the function,
which means you must know in advance what kind of record will be
returned.

CREATE FUNCTION foo() RETURNS SETOF record AS $$
DECLARE
    retval  record;
BEGIN
    SELECT INTO retval 1::integer, 'abc'::text;
    RETURN NEXT retval;

    SELECT INTO retval 2::integer, 'def'::text;
    RETURN NEXT retval;

    RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM foo();
ERROR:  a column definition list is required for functions returning
"record"

SELECT * FROM foo() AS foo(x integer, t text);
 x |  t
---+-----
 1 | abc
 2 | def
(2 rows)

Another possibility would be to return a cursor.  You wouldn't have
to know the record structure in advance, but you also wouldn't be
able to use the function as part of a larger query (somebody please
correct me if I'm mistaken).

CREATE FUNCTION foo(refcursor) RETURNS refcursor AS $$
BEGIN
    OPEN $1 FOR SELECT 1::integer, 'abc'::text
                UNION
                SELECT 2::integer, 'def'::text;
    RETURN $1;
END;
$$ LANGUAGE plpgsql;

BEGIN;
SELECT foo('curs');

FETCH ALL FROM curs;
 int4 | text
------+------
    1 | abc
    2 | def
(2 rows)

COMMIT;

--
Michael Fuhr

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org