Re: Returning multiple columns with a function??

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();