Thread: 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

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:

> 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