Re: 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