Creating Custom columns in stored procedure postgres

Question

I am using a stored procedure to return the type of student that is enrolled at my college. Pushing their ID through should return their first name and last name in a new column that is going to be made(Ex: commuter, employee, resident). I keep getting an error:

ERROR:  syntax error at or near "if"
LINE 8:     if exists (select count(commuterid) > 0 from commuter wh...).

Any tips or ideas?

create or replace function roleAtMarist(int, REFCURSOR) returns refcursor as
$$
declare
   identifier      int := $1;
   resultset refcursor := $2;
 begin
  open resultset for
    if exists (select count(commuterid) > 0 from commuter where commuterid = identifier) then
      select fname, lname, "Commuter" as Role
      from people 
      where peopleid = identifier;
    end if;

    if exists (select count(employeeid) > 0 from employee where emplpoyeeid = identifier) then
      select fname, lname, "Employee" as Role
      from people 
      where peopleid = identifier;
    end if;

    if exists (select count(residentid) > 0 from studentpark where residentid = identifier) then
      select fname, lname, "Resident" as Role
      from people 
      where peopleid = identifier;
    end if;
return resultset;
end; 
$$
language plpgsql; 
select roleAtMarist(12, 'resultset') ;
fetch all from results ;

Erwin Brandstetter · Accepted Answer · 2016-04-25T04:05:45.933

0

This is backwards in multiple ways. A cursor would use valid SQL and no plpgsql commands. But you don't need a cursor nor plpgsql to begin with. A simple SQL function should do:

CREATE OR REPLACE FUNCTION role_at_marist(_identifier int)
  RETURNS TABLE (fname text, lname text, "role" text) AS
$func$
   SELECT p.fname, p.lname, text 'Commuter'
   FROM   people 
   WHERE  p.peopleid = $1
   AND    EXISTS (SELECT 1 FROM commuter c WHERE c.commuterid = p.peopleid)

   UNION ALL
   SELECT p.fname, p.lname, 'Employee'
   FROM   people 
   WHERE  p.peopleid = $1
   AND    EXISTS (SELECT 1 FROM employee e WHERE e.emplpoyeeid = p.peopleid)

   UNION ALL
   SELECT p.fname, p.lname, 'Resident'
   FROM   people 
   WHERE  p.peopleid = $1
   AND    EXISTS (SELECT 1 FROM studentpark s WHERE s.residentid = p.peopleid)
$func$ LANGUAGE sql;

Call:

SELECT * FROM role_at_marist(12);

Set-returning functions can be used just like tables in the FROM list.
String literals are enclosed in single quotes!

edited Apr 25 '16 at 04:05

answered Apr 25 '16 at 03:36

Erwin Brandstetter

605,456
145
1,078
1,228

I ran that code and received an error: column "commuter" does not exist. – Zeke Apr 25 '16 at 03:48
this column is not in people but I understand where you tried to access it from...role_at_marist. – Zeke Apr 25 '16 at 03:49
@Zeke. That's what you do in your code: `select fname, lname, "Commuter" as Role from people`. I assume you are aware that double-quoted identifiers are case-sensitive? http://stackoverflow.com/a/20880247/939860 – Erwin Brandstetter Apr 25 '16 at 03:50
i edited the code and when I call the select statement, I receive a "type mismatch error" because there is no integer type in role_at_marist... only text. @erwin Brandstetter yes I am also aware of that! – Zeke Apr 25 '16 at 03:55
@Zeke: I assume you used the wrong quotes. Consider the update. – Erwin Brandstetter Apr 25 '16 at 04:08

Creating Custom columns in stored procedure postgres

1 Answers1