Home > mailing lists

function related problem - Mailing list pgsql-admin

From	shruti shruti
Subject	function related problem
Date	June 3, 2009 09:42:40
Msg-id	dc17571b0906030242j684b825ehc017f20284f77f40@mail.gmail.com Whole thread Raw
List	pgsql-admin

Tree view

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

)

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;

pgsql-admin by date:

From: Kenny W Drobnack
Date: 01 June 2009, 19:03:23
Subject: Re: rotating log files and adding timestamps to postgres logs

From: Lewis Kapell
Date: 05 June 2009, 15:52:17
Subject: Postgres has stopped logging

function related problem - Mailing list pgsql-admin

Previous

Next