Обсуждение: pl/pgsql and global variables

Hello,

We are trying to select which procedural language to use with postgresql. We
are familiar with oracle and PL/SQL and were pleased with the close match
with PL/pgSQL.

However we need access to global variables in PL/pgSQL. I cannot find any
mention of this in the docs. A search on google was not helpful as it just
pointed back at the docs. Is this feature available, or do we have to use
some of the other available languages such as PL/TCL or PL/Python?

Also any advice on the stability of any of the PL/languages would be
appreciated.

Thanks again for reading and answering this.

John Lim

John Lim wrote:
> However we need access to global variables in PL/pgSQL. I cannot find any
> mention of this in the docs. A search on google was not helpful as it just
> pointed back at the docs. Is this feature available, or do we have to use
> some of the other available languages such as PL/TCL or PL/Python?

There are no global variables natively available to PL/pgSQL. If global
across function calls, but within one backend connection, is what you
need, it would be fairly easy to write a C function to provide the
capability and then use it from PL/pgSQL.

I don't know a great deal about PL/Tcl, PL/Perl, and PL/Python, but I
believe they all have the ability to create and reference global
variables within one backend connection. I am relatively certain none
allow globals across backends.

HTH,

Joe

so what environment should the global variables be in, the PL/PGSQL
environement, or the server environment?

John Lim wrote:
> Hello,
>
> We are trying to select which procedural language to use with postgresql. We
> are familiar with oracle and PL/SQL and were pleased with the close match
> with PL/pgSQL.
>
> However we need access to global variables in PL/pgSQL. I cannot find any
> mention of this in the docs. A search on google was not helpful as it just
> pointed back at the docs. Is this feature available, or do we have to use
> some of the other available languages such as PL/TCL or PL/Python?
>
> Also any advice on the stability of any of the PL/languages would be
> appreciated.
>
> Thanks again for reading and answering this.
>
> John Lim
>
>
> ---------------------------(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
>

"John Lim" <heyjohnlim@yahoo.com> writes:
> However we need access to global variables in PL/pgSQL. I cannot find any
> mention of this in the docs.

That's because it's not there.  However, if the feature exists in Oracle
then it would seem reasonable to add it to PL/pgSQL.  Want to volunteer?

> Also any advice on the stability of any of the PL/languages would be
> appreciated.

plpgsql is by far the best-tested and most widely used, I believe.
If I had to choose a second-best, I'd probably bet on pltcl, which is
the oldest of the bunch and probably better-wrung-out than the others.

            regards, tom lane

Use plpython.  There you will have session duration
variables available. If you need multi-connection
"global" variables, put them in a table (duh :-)

elein@varlena.com

On Wednesday 09 April 2003 04:24, John Lim wrote:
> Hello,
>
> We are trying to select which procedural language to use with postgresql.
> We are familiar with oracle and PL/SQL and were pleased with the close
> match with PL/pgSQL.
>
> However we need access to global variables in PL/pgSQL. I cannot find any
> mention of this in the docs. A search on google was not helpful as it just
> pointed back at the docs. Is this feature available, or do we have to use
> some of the other available languages such as PL/TCL or PL/Python?
>
> Also any advice on the stability of any of the PL/languages would be
> appreciated.
>
> Thanks again for reading and answering this.
>
> John Lim
>
>
> ---------------------------(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

--
----------------------------------------------------------------------------------------
elein@varlena.com     Database Consulting     www.varlena.com
    In memoriam Dr. Anita Borg http://www.iwt.org/home.html

How much global? global for transaction, session(connection), whole
database... Sometime good solution store "global variable" in table.

John Lim wrote:
> Hello,
>
> We are trying to select which procedural language to use with postgresql. We
> are familiar with oracle and PL/SQL and were pleased with the close match
> with PL/pgSQL.
>
> However we need access to global variables in PL/pgSQL. I cannot find any
> mention of this in the docs. A search on google was not helpful as it just
> pointed back at the docs. Is this feature available, or do we have to use
> some of the other available languages such as PL/TCL or PL/Python?
>
> Also any advice on the stability of any of the PL/languages would be
> appreciated.
>
> Thanks again for reading and answering this.
>
> John Lim
>
>

--
Olleg Samojlov

Thanks for all the answers.

To summarize, session variables are available for TCL/Python, but not
PL/PgSQL.
Unlike Oracle, there are no "global application variables", just store data
in a table.

Tom Lane also mentioned that the pgSQL language is the most tested.

Cheers, John

"John Lim" <heyjohnlim@yahoo.com> wrote in message
news:b70vmt$uhp$1@news.hub.org...
> Hello,
>
> We are trying to select which procedural language to use with postgresql.
We
> are familiar with oracle and PL/SQL and were pleased with the close match
> with PL/pgSQL.
>
> However we need access to global variables in PL/pgSQL. I cannot find any
> mention of this in the docs. A search on google was not helpful as it just
> pointed back at the docs. Is this feature available, or do we have to use
> some of the other available languages such as PL/TCL or PL/Python?
>
> Also any advice on the stability of any of the PL/languages would be
> appreciated.
>
> Thanks again for reading and answering this.
>
> John Lim
>
>