Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2011

    Unanswered: return select statement in multiple columns when using a function

    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.

    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;
    This seems to work; however, it is returning the results in a single column instead of multiple columns.

    any help would be appreciated.
    Last edited by skyStream; 08-15-11 at 15:24.

  2. #2
    Join Date
    Jun 2004
    Arizona, USA
    I believe you'll need to return a table type with the function - it looks like you're currently returning an array.

    Ref the documention here
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts