function related problem

[shruti shruti <s.shruti1212@xxxxxxxxx>]

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;