Thanks a lot Jayadevan.
I was unaware of temp table kind of functionality exists in postgres.
Now i updated functions as follows-
I have one question - if is there any better way of checking if temporary table already created for the given session package(other than one i used to capture as exception).
###################################################################
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- Package specific
CREATE OR REPLACE FUNCTION "MM".Pack_Spec_THMS_Pack()
RETURNS numeric AS
$BODY$
DECLARE
v_output numeric := 1 ;
v_SessionID character varying(500) ;
v_packName character varying(50) := 'THMS_Pack';
v_cnt numeric := 0 ;
v_tmp boolean := false ;
BEGIN
begin
create temporary table Tmp_Package_Variable_Table
(
Var_Name character varying(50),
Var_Value character varying(50),
Var_DataType character varying(50),
Var_Type character varying(50)--public or private
);
exception
when others then return 0 ;
end ;
-- insert global variables for package with their initial values
-- Glbl_Var_number
insert into Tmp_Package_Variable_Table
(
Var_Name,
Var_Value,
Var_DataType,
Var_Type
)
values (
'Glbl_Var_number',
'10',
'numeric',
'public'
);
-- Glbl_Var_char
insert into Tmp_Package_Variable_Table
(
Var_Name,
Var_Value,
Var_DataType,
Var_Type
)
values (
'Glbl_Var_char',
null,
'character varying',
'public'
);
-- insert private variables for package with their initial values
-- Locl_Var_number
insert into Tmp_Package_Variable_Table
(
Var_Name,
Var_Value,
Var_DataType,
Var_Type
)
values (
'Locl_Var_number',
'20',
'numeric',
'private'
);
-- Locl_Var_char
insert into Tmp_Package_Variable_Table
(
Var_Name,
Var_Value,
Var_DataType,
Var_Type
)
values (
'Locl_Var_char',
'localpack',
'character varying',
'private'
);
RETURN v_output;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE FUNCTION "MM".GetPublicVar_THMS_Pack( v_VariableName character varying )
RETURNS character varying AS
$BODY$
DECLARE
v_output character varying(500) ;
v_cnt numeric := 0;
v_tmp numeric := 0;
BEGIN
--set package initializtion
v_tmp := "MM".Pack_Spec_THMS_Pack();
select count(1)
into v_cnt
from Tmp_Package_Variable_Table
where Var_Name = v_VariableName
and Var_Type = 'public'
;
if v_cnt>0 then
select Var_Value
into v_output
from Tmp_Package_Variable_Table
where Var_Name = v_VariableName
and Var_Type = 'public'
;
else
v_output := null;
end if;
RETURN v_output;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
CREATE OR REPLACE FUNCTION "MM".SetPublicVar_THMS_Pack( v_VariableName character varying, v_VariableValue character varying)
RETURNS numeric AS
$BODY$
DECLARE
v_output numeric := 1;
v_cnt numeric := 0;
v_tmp numeric := 0 ;
BEGIN
--set package initializtion
v_tmp := "MM".Pack_Spec_THMS_Pack();
update Tmp_Package_Variable_Table
set Var_Value = v_VariableValue
where Var_Name = v_VariableName
and Var_Type = 'public'
;
RETURN v_output;
END;
$BODY$ LANGUAGE 'plpgsql' VOLATILE
------------------------------------------------------------------------------
####################################################################
Thanks again.
Venkat
=====-----=====-----===== Notice: The information contained in this e-mail message and/or attachments to it may contain confidential or privileged information. If you are not the intended recipient, any dissemination, use, review, distribution, printing or copying of the information contained in this e-mail message and/or attachments to it are strictly prohibited. If you have received this communication in error, please notify us by reply e-mail or telephone and immediately and permanently delete the message and any attachments. Thank you
