--Table to hold public and private package variables
------------------------------------------------
create table "MM".Package_Variable_Table
(
packageName character varying(50),
session_id character varying(200), -- This is unique session id 
Var_Name character varying(50),
Var_Value character varying(50),
Var_Type character varying(50)--public or private
);
------------------------------------------------
--query to get unique session id
SELECT procpid||'.'||backend_start
FROM pg_stat_activity
WHERE datname = current_database()
AND usename = session_user
AND client_addr = inet_client_addr()
AND client_port = inet_client_port()
;
------------------------------------------------
-- function for getting unique session id
CREATE OR REPLACE FUNCTION "MM".Current_Session()
  RETURNS character varying AS
$BODY$
DECLARE
    v_output character varying(500) := null ;
BEGIN

    SELECT procpid||'.'||backend_start
      into v_output
    FROM pg_stat_activity
    WHERE datname = current_database()
      AND usename = session_user
      AND client_addr = inet_client_addr()
      AND client_port = inet_client_port()
    ;
    
    RETURN v_output;
END;
$BODY$   LANGUAGE 'plpgsql' VOLATILE
------------------------------------------------
-- function to check if given session is valid
CREATE OR REPLACE FUNCTION "MM".IsValidSession( sid character varying )
  RETURNS boolean AS
$BODY$
DECLARE
    v_output boolean := true ;
    v_cnt numeric := 0 ;
BEGIN

    if exists(select 1 from  pg_stat_activity where procpid||'.'||backend_start = sid ) then
	v_output := true ;
    else 
        v_output := false ;
    end if ;      
    
    RETURN v_output;
END;
$BODY$   LANGUAGE 'plpgsql' VOLATILE
----------------------------------------------------
--function for checking given session package is loaded or not
CREATE OR REPLACE FUNCTION "MM".IsPackLoaded( sid character varying )
  RETURNS boolean AS
$BODY$
DECLARE
    v_output boolean := true ;
    v_cnt numeric := 0 ;    
    v_packName  character varying(50) :=  'THMS_Pack';
BEGIN

    select count(1)
       into v_cnt
    from "MM".Package_Variable_Table
    where session_id = sid
    ;   
    
    if v_cnt >0 then
	v_output := true ;
    else
        v_output := false ;
    end if ;
       
    
    RETURN v_output;
END;
$BODY$   LANGUAGE 'plpgsql' VOLATILE
----------------------------------------------------
-- to delete invalid session' package variable data
CREATE OR REPLACE FUNCTION "MM".Delete_invld_pack_data(  )
  RETURNS boolean AS
$BODY$
DECLARE
    v_output boolean := true ;
    v_cnt1 numeric := 0 ;
    v_cnt2 numeric := 0 ;
    v_packName  character varying(50) :=  'THMS_Pack';
BEGIN

    delete from "MM".Package_Variable_Table p
    where "MM".IsValidSession(p.session_id )= false
    ;
       
    
    RETURN v_output;
END;
$BODY$   LANGUAGE 'plpgsql' VOLATILE
----------------------------------------------------
--++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
-- 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

    --Clear ended session data from tables
    v_tmp := "MM".Delete_invld_pack_data(  );
   
    -- get current session
    v_SessionID := "MM".Current_Session();
    /*
    check if this session id is already present in 
    global variable table, if present--> no need to insert.
    otherwise need to insert global and 
    */

    if "MM".IsPackLoaded(v_SessionID) = true then 
       null ;		
    else
       -- insert global variables for package with their initial values	
       -- Glbl_Var_number
       insert into "MM".Package_Variable_Table
		(
		packageName,
		session_id,
		Var_Name,
		Var_Value,
		Var_Type
		)
       values (
		v_packName,
		v_SessionID,
		'Glbl_Var_number',
		'10',
		'public'			
	      );

       -- Glbl_Var_char
       insert into "MM".Package_Variable_Table
		(
		packageName,
		session_id,
		Var_Name,
		Var_Value,
		Var_Type
		)
       values (
		v_packName,
		v_SessionID,
		'Glbl_Var_char',
		 null,
		'public'			
	      ); 

       -- insert private variables for package with their initial values	
       -- Locl_Var_number
       insert into "MM".Package_Variable_Table
		(
		packageName,
		session_id,
		Var_Name,
		Var_Value,
		Var_Type
		)
       values (
		v_packName,
		v_SessionID,
		'Locl_Var_number',
		'20',
		'private'			
	      );

       -- Locl_Var_char
       insert into "MM".Package_Variable_Table
		(
		packageName,
		session_id,
		Var_Name,
		Var_Value,
		Var_Type
		)
       values (
		v_packName,
		v_SessionID,
		'Locl_Var_char',
		 'localpack',
		'private'			
	      );   
    end if ;
            
    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_packName  character varying(50) :=  'THMS_Pack';
    v_SessionID character varying(200) ;
    v_tmp numeric := 0;
BEGIN
    --set package initializtion
    v_tmp := "MM".Pack_Spec_THMS_Pack();
    
    v_SessionID := "MM".Current_Session();

    select count(1)
      into v_cnt
    from "MM".Package_Variable_Table
    where session_id = v_SessionID
      and packageName = v_packName
      and Var_Name = v_VariableName
      and Var_Type = 'public'
    ;
    if v_cnt>0 then
	select Var_Value
	  into v_output
	  from "MM".Package_Variable_Table
	where session_id = v_SessionID
          and packageName = v_packName
          and 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_packName  character varying(50) :=  'THMS_Pack';
    v_SessionID character varying(200) ;
    v_tmp numeric := 0 ;
BEGIN
    --set package initializtion
    v_tmp := "MM".Pack_Spec_THMS_Pack();
    
    v_SessionID := "MM".Current_Session();

    update "MM".Package_Variable_Table
       set Var_Value = v_VariableValue
   where session_id = v_SessionID
          and packageName = v_packName
          and Var_Name = v_VariableName
          and Var_Type = 'public'
    ;
       
    
    RETURN v_output;
END;
$BODY$   LANGUAGE 'plpgsql' VOLATILE
------------------------------------------------------------------------------

--Testing : in different sessions, these should return different values 
select  "MM".GetPublicVar_THMS_Pack('Glbl_Var_number')
select "MM".SetPublicVar_THMS_Pack( 'Glbl_Var_number', '-1056')

select  "MM".GetPublicVar_THMS_Pack('Glbl_Var_number')

--
select * from "MM".Package_Variable_Table

select  "MM".GetPublicVar_THMS_Pack('Glbl_Var_char')
select "MM".SetPublicVar_THMS_Pack( 'Glbl_Var_char', 'avcd')

select  "MM".GetPublicVar_THMS_Pack('Glbl_Var_char')

--
select * from "MM".Package_Variable_Table


