[ADMIN] function related problem

[shruti shruti]

I have change project database from oracle to postgresql.
And I face problem in cursor declaration, can u please sort out that.
I attached my functions also...

Regards,

Oracle function


PROCEDURE Procdeptcombo(

Isvalid IN VARCHAR2,

Hcode IN VARCHAR2,

Rostertype VARCHAR2,

Seatid VARCHAR2,

Ipaddress VARCHAR2,

Err OUT VARCHAR2,

Resultset OUT Ahis_Type.refcursor

)

AS

QUERY VARCHAR2 (4000);

Tname VARCHAR2(50);

Cname VARCHAR2(50);

BEGIN

Tname:='GBLT_DEPARTMENT_MST';

Cname:='GNUM_DEPT_CODE';

QUERY := 'SELECT DISTINCT SUBSTR (a.hopnum_deptunitcode, 1, 3),
b.gstr_dept_name

FROM HOPT_DEPT_UNIT_ROSTER_DTL a, GBLT_DEPARTMENT_MST b

WHERE a.hopdt_start_DT <= SYSDATE AND a.hopdt_end_DT >= SYSDATE AND
b.gnum_dept_code
= SUBSTR (a.hopnum_deptunitcode, 1, 3)

AND b.gnum_hospital_code = a.gnum_hospital_code AND gdt_effective_frm <=TRUNC
(SYSDATE) AND NVL (gdt_effective_to, SYSDATE + 1) >= TRUNC (SYSDATE)

AND a.gnum_isvalid = ' || isvalid || '

AND a.gnum_hospital_code = ' || hcode || '

AND a.hgnum_roster_type=' || rostertype|| '

AND b.gnum_dept_code IN ( SELECT gnum_column_value FROM
GBLT_ROLE_SEAT_TABLE_DTL P, GBLT_METATABLE_COLUMN_MST q

WHERE P.gnum_metatable_id = q.gnum_metatable_id

AND gstr_table_name =trim('' ' || tname ||''')

AND gstr_column_name = trim(''' || cname || ''')

AND P.gnum_seatid =Pkg_Usermgmt.fun_getseatid(' || seatid || ',' || hcode ||
')

AND P.gnum_hospital_code = q.gnum_hospital_code

AND P.gnum_hospital_code =' || hcode || ' ) ORDER BY b.gstr_dept_name ';

OPEN resultset

FOR QUERY;

EXCEPTION

WHEN OTHERS

THEN

IF resultset%ISOPEN

THEN

CLOSE resultset;

END IF;

Err := SQLERRM;

RAISE;

END Procdeptcombo;

Postgresql function


CREATE OR REPLACE FUNCTION procdeptcombo(IN isvalid character varying, IN
hcode character varying, IN rostertype character varying, IN seatid
character varying, IN ipaddress character varying, OUT resultset refcursor)
  RETURNS refcursor AS
$BODY$

 DECLARE
  Tname TEXT;
  Cname TEXT;
 BEGIN
  Tname:='GBLT_DEPARTMENT_MST';
  Cname:='GNUM_DEPT_CODE';
  OPEN resultset
  FOR SELECT DISTINCT SUBSTR (a.hopnum_deptunitcode::VARCHAR, 1, 3),
b.gstr_dept_name
                     FROM HOPT_DEPT_UNIT_ROSTER_DTL  a, GBLT_DEPARTMENT_MST
b
                     WHERE a.hopdt_start_DT <= CURRENT_DATE AND
a.hopdt_end_DT >= CURRENT_DATE AND  b.gnum_dept_code::VARCHAR = SUBSTR
(a.hopnum_deptunitcode::VARCHAR, 1, 3)
       AND b.gnum_hospital_code = a.gnum_hospital_code
       AND gdt_effective_frm <=  (CURRENT_DATE)
       AND COALESCE(CASE WHEN gdt_effective_to=NULL THEN (CURRENT_DATE+1)
ELSE gdt_effective_to END,(CURRENT_DATE + 1)) >= (CURRENT_DATE)
      AND a.gnum_isvalid= isvalid::numeric
      AND a.gnum_hospital_code =  hcode::numeric
      AND a.hgnum_roster_type=   rostertype::numeric
                    AND b.gnum_dept_code::VARCHAR IN (SELECT
gnum_column_value FROM GBLT_ROLE_SEAT_TABLE_DTL P,
GBLT_METATABLE_COLUMN_MST q
                    WHERE P.gnum_metatable_id = q.gnum_metatable_id
   AND  gstr_table_name =  tname
   AND gstr_column_name = cname
   AND P.gnum_seatid =fun_getseatid( seatid::numeric ,  hcode::numeric  )
   AND P.gnum_hospital_code = q.gnum_hospital_code
   AND P.gnum_hospital_code =  hcode::numeric ) ORDER BY b.gstr_dept_name;


END; $BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION procdeptcombo(character varying, character varying, character
varying, character varying, character varying) OWNER TO postgres;