sql - Creating Custom columns in stored procedure postgres -
i using stored procedure return type of student enrolled @ college. pushing id through should return first name , last name in new column going made(ex: commuter, employee, resident). keep getting error:
error: syntax error @ or near "if" line 8: if exists (select count(commuterid) > 0 commuter wh...).
any tips or ideas?
create or replace function roleatmarist(int, refcursor) returns refcursor $$ declare identifier int := $1; resultset refcursor := $2; begin open resultset if exists (select count(commuterid) > 0 commuter commuterid = identifier) select fname, lname, "commuter" role people peopleid = identifier; end if; if exists (select count(employeeid) > 0 employee emplpoyeeid = identifier) select fname, lname, "employee" role people peopleid = identifier; end if; if exists (select count(residentid) > 0 studentpark residentid = identifier) select fname, lname, "resident" role people peopleid = identifier; end if; return resultset; end; $$ language plpgsql; select roleatmarist(12, 'resultset') ; fetch results ;
this backwards in multiple ways. cursor use valid sql , no plpgsql commands. don't need cursor nor plpgsql begin with. simple sql function should do:
create or replace function role_at_marist(_identifier int) returns table (fname text, lname text, "role" text) $func$ select p.fname, p.lname, text 'commuter' people p.peopleid = $1 , exists (select 1 commuter c c.commuterid = p.peopleid) union select p.fname, p.lname, 'employee' people p.peopleid = $1 , exists (select 1 employee e e.emplpoyeeid = p.peopleid) union select p.fname, p.lname, 'resident' people p.peopleid = $1 , exists (select 1 studentpark s s.residentid = p.peopleid) $func$ language sql; call:
select * role_at_marist(12); set-returning functions can used tables in from list.
string literals enclosed in single quotes!
Comments
Post a Comment