title: How can a Postgres Stored Function return a table
body: <p>I would like to find out how a Postgres stored function can return a table, with identified columns. I've used returns setof returnType:</p>
  
  <pre><code>-- create employeeSearchResult returnType
  create type employeeAllReturnType as
  (
    id bigserial,
    "positionId" integer,
    "subjectId" bigint,
    "dateEngaged" date,
    "nextKin" text,
    "nrcNo" text,
    dob date,
    father text,
    mother text,
    wife text,
    "userId" integer,
    "statusId" integer,
    "mainCode" text,
    "subCode" text
  );
  
  
  -- Search for emmployee by name
  CREATE OR REPLACE FUNCTION "employee_search_by_name"(employeeNameIN text)
  returns setof employeeAllReturnType as 
  $$
  declare
      results record;
      resultsRow employee%rowtype;
      nameIn text;
  begin
      nameIn = employeeNameIN || '%';
      for results in select 
          employee.id,-- bigserial NOT NULL,
    employee."positionId",-- integer,
    employee."subjectId",-- bigint NOT NULL,
    employee."dateEngaged",-- date,
    employee."nextKin",-- text,
    employee."nrcNo",-- text,
    employee.dob,-- date,
    employee.father,-- text,
    employee.mother,-- text,
    employee.wife,-- text,
    employee."userId",-- integer NOT NULL,
    employee."statusId",-- integer,
    employee."mainCode",-- character(5) NOT NULL,
    employee."subCode"-- character(10),
       from employee, subject where employee."subjectId" = subject.id and (subject.name1 ILIKE nameIn OR subject.name2 ILIKE nameIn OR subject.name3 ILIKE nameIn OR subject.name4 ILIKE nameIn) loop
        return next results;
      end loop;
  end;
  $$ language 'plpgsql';
  </code></pre>
  
  <p>and also return table():</p>
  
  <pre><code>-- Search for emmployee by name
  CREATE OR REPLACE FUNCTION "employee_search_by_name"(employeeNameIN text)
  returns table (id bigserial,
    position integer,
    subject bigint,
    date_engaged date,
    next_kin text,
    nrc_no text,
    dob date,
    father text,
    mother text,
    wife text,
    user_id integer,
    status_id integer,
    main_code text,
    sub_code text) as 
  $$
  declare
      results record;
      resultsRow employee%rowtype;
      nameIn text;
  begin
      nameIn = employeeNameIN || '%';
      for results in select 
          employee.id,-- bigserial NOT NULL,
          employee."positionId",-- integer,
          employee."subjectId",-- bigint NOT NULL,
          employee."dateEngaged",-- date,
          employee."nextKin",-- text,
          employee."nrcNo",-- text,
          employee.dob,-- date,
          employee.father,-- text,
          employee.mother,-- text,
          employee.wife,-- text,
          employee."userId",-- integer NOT NULL,
          employee."statusId",-- integer,
          employee."mainCode",-- character(5) NOT NULL,
          employee."subCode"-- character(10),
       from employee, subject where employee."subjectId" = subject.id and (subject.name1 ILIKE nameIn OR subject.name2 ILIKE nameIn OR subject.name3 ILIKE nameIn OR subject.name4 ILIKE nameIn) loop
        return next results;
      end loop;
  end;
  $$ language 'plpgsql';
  </code></pre>
  
  <p>But both have outputs in the following formats:</p>
  
  <pre><code>"(1,1,1,2011-12-01,Timea,fg1254,1981-12-27,moses,sarada,timea,1,1,"ADM  ","1         ")"
  "(37,3,10,2011-11-11,s,s,2011-11-11,s,s,s,1,1,"OP   ","1         ")"
  </code></pre>
  
  <p>Is there anyway in which I can have outputs such as those of a select result from a table?</p>
  
  <pre><code>"1";1;1;"2011-12-01";"Timea";"fg1254";"1981-12-27";"moses";"sarada";"timea";1;1;"ADM  ";"1         "
  </code></pre>
  
  <p>Such that handling result data from the front end wont require a parser.</p>