Three examples of database PostgreSQL functions (stored procedures)

Example 1:

-- Function: getopngrd (numeric) </P> <p> -- drop function getopngrd (numeric); </P> <p> Create or replace function getopngrd (opnsq numeric) <br/> Returns character varying as <br/> $ body $ <br/> declare <br/> rec record; <br/> opngrd "varchar": = ''; <br/> SQL "varchar"; <br/> begin <br/> SQL: = 'select opn_grd from t_opn_grd_asg where opn_sq = '| opnsq | 'order by opn_grd ASC'; <br/> for REC in execute s QL loop <br/> opngrd: = opngrd | Rec. opn_grd | ','; <br/> end loop; <br/> If opngrd! = ''Then <br/> opngrd: = substring (opngrd, 0, character_length (opngrd); <br/> end if; <br/> return opngrd; <br/> end; <br/> $ body $ <br/> language 'plpgsql' volatile <br/> cost 100; <br/> alter function getopngrd (numeric) owner to ipvs;

 

Example 2:

-- Function: getfacdpt (numeric, character varying, character varying) </P> <p> -- drop function getfacdpt (numeric, character varying, character varying ); </P> <p> Create or replace function getfacdpt (opnsq numeric, categorycd character varying, schoolid character varying) <br/> Returns character varying as <br/> $ body $ <br/> declare <br/> rec record; <br/> facdpt "varchar": = ''; <br/> SQL "varchar"; <br/> Begin </P> <p> SQL: = 'select t1.org _ unit_cd, t2.orgunit _ nM, t2.orgunit _ SNM '<br/> |' from t_opn_orgunit_ast T1 '<br/> | 'inner join t_orgunit T2 on t1.org _ unit_cd = t2.org _ unit_cd and t1.school _ id = t2.school _ id' <br/> | 'where t1.opn _ sq = '| opnsq <br/> |' and t1.opn _ category_cd =/''| categorycd |' /''<br/> | 'and t1.school _ id =/'' | schoolid |'/''<br/> | 'order by t1.org _ unit_cd ASC '; </P> <p> for REC in Execute SQL loop <br/> facdpt: = facdpt | rec. orgunit_nm | ','; <br/> end loop; <br/> If facdpt! = ''Then <br/> facdpt: = substring (facdpt, 0, character_length (facdpt); <br/> end if; <br/> return facdpt; <br/> end; <br/> $ body $ <br/> language 'plpgsql' volatile <br/> cost 100; <br/> alter function getfacdpt (numeric, character varying, character varying) owner to S;

 

Example 3:

-- Function: getopntmasg (numeric) </P> <p> -- drop function getopntmasg (numeric); </P> <p> Create or replace function getopntmasg (opnsq numeric) <br/> Returns character varying as <br/> $ body $ <br/> declare <br/> rec record; <br/> opntmasg "varchar": = ''; <br/> SQL "varchar"; <br/> begin <br/> SQL: = 'select distinct t1.opn _ SQ, t1.opn _ day_of_wk_dv as opn_day_of_wk_dv, '<br/> | 't2.day _ of_wk_nm as day_of _ Wk_nm, t1.opn _ stt_prd_crd_cd as hour, '<br/> | 't1.opn _ prd_crd_lg as opn_prd_crd_lg, t1.mn _ opn_tm_flg as mn_opn_tm_flg, '<br/> | 't1.prd _ crd_ord as prd_crd_ord, t1.prd _ ord as prd_ord' <br/> | 'from t_opn_tm_asg T1, t_day_of_wk T2 where t1.opn _ sq = '| opnsq <br/> |' and t1.opn _ day_of_wk_dv = t2.day _ of_wk_dv order by t1.opn _ day_of_wk_dv ASC '; <br/> for REC in Execute SQL loo P <br/> If rec. prd_ord + ceiling (Rec. opn_prd_crd_lg/2)-1 = rec. prd_ord then <br/> opntmasg: = opntmasg | substring (Rec. day_of_wk_nm, 0, 2) | rec. prd_ord | ','; <br/> else <br/> opntmasg: = opntmasg | substring (Rec. day_of_wk_nm, 0, 2) | rec. prd_ord | '-' | rec. prd_ord + ceiling (Rec. opn_prd_crd_lg/2)-1 | ','; <br/> end if; </P> <p> end loop; <br/> If opntmasg! = ''Then <br/> opntmasg: = substring (opntmasg, 0, character_length (opntmasg); <br/> end if; <br/> return opntmasg; <br/> end; <br/> $ body $ <br/> language 'plpgsql' volatile <br/> cost 100; <br/> alter function getopntmasg (numeric) owner to ipvs;