return select statement in multiple columns when using a function

skyStream · #1 (**permalink**) 08-15-11, 15:09

Greetings all

I am new to postgresql and have been attempting to create a function in which I can specifiy a variable for it to use in the select statement.

here is what I have so far.

Code:

create type type_f_test as (project_id int, hospital_name text, project_name text, project_type text, project_status text);

create or replace function f_test(integer)
returns setof type_f_test as 

$$
select a.project_id, b.hospital_name, a.project_name, a.project_type, a.project_status  

  from t_projects a 
  join t_hospitals b 
  on a.hospital_id = b.hospital_id 

  join r_projects_users c 
  on a.project_id = c.project_id 

  where a.is_deleted = 0 
  and c.is_deleted = 0 
  and a.project_status <> 'Closed' 
  and a.project_status <> 'Completed' 
  and c.user_id = $1
  order by b.hospital_name, a.project_name asc;
$$

LANGUAGE SQL;

This seems to work; however, it is returning the results in a single column instead of multiple columns.

any help would be appreciated.