Thread: Returning multiple columns with a function??
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?
Sincerley,
Joshua Drake
--
<COMPANY>CommandPrompt - http://www.commandprompt.com </COMPANY>
<CONTACT> <PHONE>+1.503.222-2783</PHONE> </CONTACT>
On Monday, December 16, 2002, at 05:48 PM, Joshua D. Drake wrote: > <snip> > 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 I think you want: RETURNS SETOF record as 'select ...' eric > > What are we missing? > > Sincerley, > > Joshua Drake > > -- > <COMPANY>CommandPrompt - http://www.commandprompt.com </COMPANY> > <CONTACT> <PHONE>+1.503.222-2783</PHONE> </CONTACT> > > > ---------------------------(end of > broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org
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();
Joshua D. Drake wrote: > 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? Try: CREATE OR REPLACE FUNCTION test_1 () RETURNS SETOF record AS 'SELECT ''a''::text, ''b''::text' LANGUAGE 'SQL'; regression=# SELECT * FROM test_1() AS t(f1 text, f2 text); f1 | f2 ----+---- a | b (1 row) or: CREATE TYPE mytype AS (f1 int, f2 text); CREATE OR REPLACE FUNCTION test_2 () RETURNS SETOF mytype AS 'SELECT 1::int, ''b''::text' LANGUAGE 'SQL'; regression=# SELECT * FROM test_2(); f1 | f2 ----+---- 1 | b (1 row) See the info scattered amongst: http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/sql-select.html http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/sql-createtype.html http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/xfunc-tablefunctions.html http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/xfunc-sql.html http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/xfunc-c.html http://www.us.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql-control-structures.html (and maybe some others) HTH, Joe
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, forexample? So far I've read the documentation and the only reference to the use of SETOF to return more than one value in a functionis 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@megazone23.bigpanda.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
On Thu, 2002-12-19 at 14:31, RenX SalomXo wrote: > 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? Yes -- set-returning functions are supported in SQL, PL/PgSQL and C as of 7.3. See the documentation for RETURN NEXT here: http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/plpgsql-control-structures.html Cheers, Neil -- Neil Conway <neilc@samurai.com> || PGP Key ID: DB3C29FC