Thread: Variable return type...
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…
I know I can use anyelement but then I would need to specify an anyelement input type (but in my case the input element would the the same all the time);
Any idea in how I could do this?
Thanks a lot!
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
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