Обсуждение: One solution for Packages in Postgre

Поиск
Список
Период
Сортировка

One solution for Packages in Postgre

От
venkatrao.b@tcs.com
Дата:

Hi Posgre Developers,

I am new to Postgre. We are migrating an oracle db to postgre. In oracle we have used so many packages.  As per my understanding, there is no oracle package like functionality in postgre. I was just trying to find some way to migrate ocale packages to postgre.
Please let me know your comments on below solution - (this is for creating a package named thms_pack script is also attached).




Common table for all packages

 table Package_Variable_Table :-
 For Storing Package public and private Variables This table will be common for all packages.
to distinguish between different sessions, it uses unique session id. Get and Set functions used to access these variables.
 
Common Functions for all packages
1) function - Current_Session()
   Function for getting current unique session id.
    Reference :  http://archives.postgresql.org/pgsql-hackers/2006-06/msg01557.php

2)function - IsValidSession( sid character varying)
common for all packages.  function for checking if given sessionid is valid,  returns true or false, if returns false then data from public and local pack table data for that sesseion can be deleted.
sid = unique session id

3)function - IsPackLoaded( sid character varying )
common for all packages.  function for checking if given sessionid 's package is initialized or not,  returns true or false, if returns false then pack initialized -> global/private var values are set.
sid = unique session id

4)function - Delete_invld_pack_data()
Common for all packages .function for deleting invalid session' public and private variable data.

Package specific additional functions

In addition to the procedure, functions in the oracle package( which will be created as functions); below additional functions will be required for each package -

1)  Pack_Spec_THMS_Pack() - function to initialze package specification if already not done (for setting any public private variable values initially). Internally this function calls Delete_invld_pack_data for deleting unnecessary data from table - Package_Variable_Table

2)GetPublicVar_THMS_Pack ( v_VariableName character varying ) One function  for getting value of all  public variable of the package. this will return char type; which can be typecasted if required. This function calls Pack_Spec_THMS_Pack().

3)SetPublicVar_THMS_Pack( v_VariableName character varying, v_VariableValue  character varying)One function for setting value of each private variable of the package. This function calls Pack_Spec_THMS_Pack().

Package functions/procedures
Each function has to call Pack_Spec_THMS_Pack() function at start, to initialize package data.

How it works:
 Whenever any procedure/function of package is called OR whenever any of the public variable is accessed( that is to be done through get or set functions ) ,
package initialization function - Pack_Spec_THMS_Pack()  is get called, it first removes unnecessary data from Package_Variable_Table table (possibly for other packages also whose session' ended). Then it checks in the table Package_Variable_Table if any entry for current session is there or not, if not it inserts new data for public/private variables and initializes.

access to public variables of package - through get /set functions.
access to private variables of package - function and procedures can directly access table Package_Variable_Table for this.



Venkat Rao Buddu
Tata Consultancy Services
Mailto: venkatrao.b@tcs.com
Website: http://www.tcs.com
____________________________________________
Experience certainty.        IT Services
                       Business Solutions
                       Outsourcing
____________________________________________
=====-----=====-----=====
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
Вложения

Re: One solution for Packages in Postgre

От
Jayadevan M
Дата:
Hi Posgre Developers,

Common table for all packages

table Package_Variable_Table :-
For Storing Package public and private Variables This table will be common for all packages.
to distinguish between different sessions, it uses unique session id. Get and Set functions used to access these variables.
 
Common Functions for all packages
1) function - Current_Session()
  Function for getting current unique session id.
   Reference :  http://archives.postgresql.org/pgsql-hackers/2006-06/msg01557.php

2)function - IsValidSession( sid character varying)
common for all packages.  function for checking if given sessionid is valid,  returns true or false, if returns false then data from public and local pack table data for that sesseion can be deleted.
sid = unique session id

3)function - IsPackLoaded( sid character varying )
common for all packages.  function for checking if given sessionid 's package is initialized or not,  returns true or false, if returns false then pack initialized -> global/private var values are set.
sid = unique session id

4)function - Delete_invld_pack_data()
Common for all packages .function for deleting invalid session' public and private variable data.

Package specific additional functions

In addition to the procedure, functions in the oracle package( which will be created as functions); below additional functions will be required for each package -

1)  Pack_Spec_THMS_Pack() - function to initialze package specification if already not done (for setting any public private variable values initially). Internally this function calls Delete_invld_pack_data for deleting unnecessary data from table - Package_Variable_Table

2)GetPublicVar_THMS_Pack ( v_VariableName character varying ) One function  for getting value of all  public variable of the package. this will return char type; which can be typecasted if required. This function calls Pack_Spec_THMS_Pack().

3)SetPublicVar_THMS_Pack( v_VariableName character varying, v_VariableValue  character varying)One function for setting value of each private variable of the package. This function calls Pack_Spec_THMS_Pack().

Package functions/procedures
Each function has to call Pack_Spec_THMS_Pack() function at start, to initialize package data.

How it works:
Whenever any procedure/function of package is called OR whenever any of the public variable is accessed( that is to be done through get or set functions ) ,
package initialization function - Pack_Spec_THMS_Pack()  is get called, it first removes unnecessary data from Package_Variable_Table table (possibly for other packages also whose session' ended). Then it checks in the table Package_Variable_Table if any entry for current session is there or not, if not it inserts new data for public/private variables and initializes.

access to public variables of package - through get /set functions.
access to private variables of package - function and procedures can directly access table Package_Variable_Table for this.

=============================================================================

By using temp tables in PostgreSQL (similar to global temp tables in Oracle. The key difference is that in PostgreSQL, you can create them in the session), you may be able to avoid the work of deleting data, storing sessions etc. Here is an example of how you can create a temp table in a session.
postgres=# \d myttmp
Did not find any relation named "myttmp".

postgres=#  select now() into myttmp;
SELECT
postgres=# \d myttmp
             Table "public.myttmp"
 Column |           Type           | Modifiers
--------+--------------------------+-----------
 now    | timestamp with time zone |

The data won't be visible to other sessions and thus your package variables not visible in other sessions. See link for details -
http://www.postgresql.org/files/documentation/books/aw_pgsql/node119.html
Please have a look at this too....
http://www.postgresql.org/docs/8.3/static/plpgsql-porting.html

Regards,
Jayadevan





DISCLAIMER:

"The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."




Re: One solution for Packages in Postgre

От
Dimitri Fontaine
Дата:
venkatrao.b@tcs.com writes:
> I am new to Postgre. We are migrating an oracle db to postgre. In
> oracle we have used so many packages.  As per my understanding, there
> is no oracle package like functionality in postgre. I was just trying
> to find some way to migrate ocale packages to postgre.

This has been discussed already in the past, see the archives. In short,
Oracle Packages are not something the PostgreSQL community intend to
offer directly, even if we have some plans to provide better tools for
managing what we have and call "extensions".

The following shows the current state of the design, the code is yet to
be written, and that could happen this very year (it obviously missed
9.0).

  http://wiki.postgresql.org/wiki/ExtensionPackaging

You will note that this document refers to C-coded extensions, but is
compatible with an extension not containing one of those. It could well
be that the feature you're missing is offered by this design.

Regards,
--
dim

Re: One solution for Packages in Postgre

От
venkatrao.b@tcs.com
Дата:

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