CODEWITHSUNDEEP
postgresql
richh
richh

Reputation: 213

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

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?

Upvotes: 21

Views: 10950

Answers (1)

xzilla
xzilla

Reputation: 1161

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

Upvotes: 38

Related Questions