Re: Returning multiple columns with a function??

Hello Stephan,

Is it possible for Pg 7.3 to have a SETOF <return_type> in a function using any other language besides sql? Pgplsql, for example?

So far I've read the documentation and the only reference to the use of SETOF to return more than one value in a function is related to functions using the sql language.

If it is not available in Pg 7.3, will it be available in future realease (7.3.1, 7.4, etc)?

CREATE TYPE my_record(id numeric, name varchar, address varchar, phone numeric);

CREATE OR REPLACE FUNCTION test_multiple() RETURNS my_record AS
'
DECLARE
my_return my_record;
BEGIN
my_return.id=0001;
my_return.name='User';
my_return.address='Address';
my_return.phone= 5555555;

RETURN my_return;
END;' LANGUAGE 'plpgsql';

On Mon, 16 Dec 2002 15:22:16 -0800 (PST)
Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com> wrote:

> On Mon, 16 Dec 2002, Joshua D. Drake wrote:
>
> > Hello,
> >
> > We are starting to test 7.3 for Mammoth (we always test a release
> > behind) and are having some problems understanding what the exact
> > features limitations of the new table functionality is. Specifically
> > in the announce (and talked about ALOT) is:
> >
> > Table Functions
> > PostgreSQL version 7.3 has greatly simplified returning result
> > sets of rows and columns in database functions. This significantly
> > enhances the useability of stored procedures in PostgreSQL, and will
> > make it even easier to port Oracle applications to PostgreSQL.
> >
> >
> > But something like this fails:
> >
> > CREATE OR REPLACE FUNCTION test_multiple () RETURNS SETOF text AS
> > 'SELECT ''a'', ''b''' LANGUAGE 'SQL';
> > ERROR: function declared to return text returns multiple columns in
> > final SELECT
> >
> > What are we missing?
>
> That's not a set of text. That's a single value of a composite row type
> (I assume you wanted two texts) ;)
>
> You can return records (but then you have to give the column defs at
> select time) or you can create a type using CREATE TYPE AS (...) and
> return that type.
>
> For example:
>
> CREATE TYPE doubletext(a text, b text);
> CREATE OR REPLACE FUNCTION test_multiple() RETURNS doubletext AS
> 'select ''a''::text, ''b''::text;' language 'sql';
> select * from test_multiple();
>
> If you potentially wanted to return multiple rows, you'd want SETOF
> doubletext, for example:
> CREATE OR REPLACE FUNCTION test_multiple2() RETURNS SETOF doubletext AS
> 'select ''a''::text, ''b''::text union select ''c''::text, ''d''::text;'
> language 'sql';
> select * from test_multiple2();
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

--
Renê Salomão
Ibiz Tecnologia -- www.ibiz.com.br