function returns multiple columns as a single column instead of multiple columns

Question

I am writing a function in PostgreSQL 9.04 attempting to use a variable which will be used in a select statement then return the results.

The statement I have is simple and works; however, all the columns are outputing to a single column instead of multiple columns.

My function:

create or replace function foo(IN pID integer, OUT project_id integer, OUT project_name    text, OUT project_type text, OUT project_description text, OUT project_status text)
returns setof record as

$$
select project_id, project_name, project_type, project_description, project_status from     t_projects
where project_id = $1;
$$

LANGUAGE SQL;

select foo(6) -- runs function

The current output looks like this:

"(6,"test project","inbound","inbound test","processing")"

How can I make it so the results are not concatenated together and return each column item separately?

Why do you have a return type of setof record while also declaring OUT columns that match the SELECT? Do you intend to return records, or populate the OUT parameters? You should pick one approach, not both. — cdhowie
– cdhowie, Commented Aug 15, 2011 at 23:41
postgresql is still new to me as I am used to working in microsoft sql erver. Ideally, I would like to return the results from the select statement. In my google searches I found that people would create a type with the designated output or would put the outputs in the function. Currently, I am still in the understanding phase of postgresql :) — richh
– richh, Commented Aug 15, 2011 at 23:58

xzilla · Accepted Answer · 2011-08-16 13:11:51Z

38

you need to call the function like this:

select * from foo(6);

which will return something like this:

project_id | project_name | project_type | project_description | project_status
-----------|--------------|--------------|---------------------|----------------
         6 | test project |      inbound |        inbound test |     processing

it's a quirk of postgres that it can be called both ways and give you a result. you might want to check the docs on set returning functions some more, there are other ways to do this as well. Oh, there is a wiki page on it, written for plpgsql, but most applies to sql functions as well: http://wiki.postgresql.org/wiki/Return_more_than_one_row_of_data_from_PL/pgSQL_functions

answered Aug 16, 2011 at 13:11

xzilla

1,1619 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Peter Krauss Over a year ago

Why select * from foo(6) is a error when defining foo() with create or replace function foo( int) returns setof record as $$ SELECT $1 as a, 'xx' as b; $$ LANGUAGE SQL; ??

xzilla Over a year ago

@Peter-Krauss Well, if you call it you get: ERROR: a column definition list is required for functions returning "record" Which means that Postgres doesn't understand what the structure of your returned values should be. You need to either add OUT params, like in the original question, or call it with a definition list:

pagila=# select * from foo(1) f (a int, b unknown);     WARNING:  column "b" has type "unknown"     DETAIL:  Proceeding with relation creation anyway.     a | b     ---+----     1 | xx     (1 row)

Collectives™ on Stack Overflow

function returns multiple columns as a single column instead of multiple columns

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related