Global/persistent variables
I'm analysing a conversion of a system from Oracle to PG. The system in
case uses a lot of PL/SQL packages and lots of them uses public/static
global/persistent variables declared inside its package specification or
body.
AFAIK PG doesn't have packages - this is not a problem since I can
handle it with different schemas or some naming convention, but is there
a way to declare persistent variables (that would be "visible" to any
function up to the end of the session) ?
TIA
Ronnie
Ronnie Meier Ramos <ronnie@viler.com.br> writes:
AFAIK PG doesn't have packages - this is not a problem since I can
handle it with different schemas or some naming convention, but is there
a way to declare persistent variables (that would be "visible" to any
function up to the end of the session) ?
plpgsql doesn't have this at present, but some of the other PLs do.
regards, tom lane
AFAIK PG doesn't have packages - this is not a problem since I can
handle it with different schemas or some naming convention, but is there
a way to declare persistent variables (that would be "visible" to any
function up to the end of the session) ?
AFAIK you'd have to fake it using a temp table.
--
Scott Ribe
scott_ribe@killerbytes.com
http://www.killerbytes.com/
(303) 665-7007 voice
I understand your pain, because PL/SQL is so close to plpgsql the functions
should just drop into quotes with renamed parameters, but they don't.
Using another language makes the port more difficult.
Have you considered functions that store the variable value in the database
and read it back from there? The functions would have to store variables
values by some kind of session or user ID so concurrent sessions wouldn't
trump one another. This would help with variable reference but variable
assignment would require more recoding.
There is also the issue of initializing sessions and cleaning up after
them. Not pretty.
Rick
Tom Lane
<tgl@sss.pgh.pa.us> To: Ronnie Meier Ramos <ronnie@viler.com.br>
Sent by: cc: pgsql-general@postgresql.org
pgsql-general-owner@pos Subject: Re: [GENERAL] Global/persistent variables
tgresql.org
01/06/2005 03:43 PM
Ronnie Meier Ramos <ronnie@viler.com.br> writes:
AFAIK PG doesn't have packages - this is not a problem since I can
handle it with different schemas or some naming convention, but is there
a way to declare persistent variables (that would be "visible" to any
function up to the end of the session) ?
plpgsql doesn't have this at present, but some of the other PLs do.
regards, tom lane
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
Import Notes
Resolved by subject fallback
Ronnie Meier Ramos wrote:
AFAIK PG doesn't have packages - this is not a problem since I can
handle it with different schemas or some naming convention, but is there
a way to declare persistent variables (that would be "visible" to any
function up to the end of the session) ?
You might be able to fake it with some C functions. See:
http://www.joeconway.com/myfunc.tgz
Look at myfunc_setvar(), myfunc_getvar(), and myfunc_rmvar().
HTH,
Joe
Thanks for all answers!
Unfortunatly, faking them with some C functions would be very complex
because in this application's case most of this variables are record
types and some are arrays (pl/tables)... :-(
It seems that creating temp tables (and some api functions) would be a
little bit easier to implement. One concern would be about preformance:
I guess that temp tables are written to disk and fetched as any other
table but discarded in the end of the session.
Any suggestion or comments?
Thanks again,
Ronnie
Joe Conway escreveu em 6/1/2005 21:57:
Show quoted text
Ronnie Meier Ramos wrote:
AFAIK PG doesn't have packages - this is not a problem since I can
handle it with different schemas or some naming convention, but is
there a way to declare persistent variables (that would be "visible"
to any function up to the end of the session) ?You might be able to fake it with some C functions. See:
http://www.joeconway.com/myfunc.tgz
Look at myfunc_setvar(), myfunc_getvar(), and myfunc_rmvar().
HTH,
Joe
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
Ronnie Meier Ramos wrote:
Unfortunatly, faking them with some C functions would be very complex
because in this application's case most of this variables are record
types and some are arrays (pl/tables)... :-(
For record types, why not just use cursors? They can be made to persist
until the end of the session. For arrays, it shouldn't be too terribly
difficult to extent the code to handle them.
Joe