One solution for Packages in Postgre - Mailing list pgsql-general
| From | venkatrao.b@tcs.com |
|---|---|
| Subject | One solution for Packages in Postgre |
| Date | |
| Msg-id | OF590C624D.E1BDCFBE-ON652576CE.001E3A60-652576CE.001E7700@tcs.com Whole thread Raw |
| Responses |
Re: One solution for Packages in Postgre
(Jayadevan M <Jayadevan.Maymala@ibsplc.com>)
Re: One solution for Packages in Postgre (Dimitri Fontaine <dfontaine@hi-media.com>) |
| List | pgsql-general |
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
Attachment
pgsql-general by date: