Re: One solution for Packages in Postgre

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."