Thread: are there static variables in pgsql?

i am writing a recursive procedure in pl/pgsql.
i need to check whether a condition is true in any step of recursive calls.

if i get a true response in one call, i wont make other recursive calls anymore, because i get what i need.
if no one gives me a true response, then uppest procedure will return false as a result.

i think i may achieve this with a static variable(shared among all calls).

is it possible?

or another solution?

thanks a lot for any help.

On Saturday 10 June 2006 06:31 pm, "Yavuz Kavus" <yavuzkavus@gmail.com> thus
communicated:
--> i am writing a recursive procedure in pl/pgsql.
--> i need to check whether a condition is true in any step of recursive
 calls. -->
--> if i get a true response in one call, i wont make other recursive calls
--> anymore, because i get what i need.
--> if no one gives me a true response, then uppest procedure will return
 false --> as a result.
-->
--> i think i may achieve this with a static variable(shared among all
 calls). -->
--> is it possible?
-->
--> or another solution?
-->
--> thanks a lot for any help.
-->
I don't know of any static variable capability; however, I use various utility
tables to accomplish this type of task. You can insert records into a table
like this while inside a transaction and the current process will be the only
process that can see it. When you are through with it, delete the record, all
within the same transaction, and your purpose will be served.

HTH...

On Sat, Jun 10, 2006 at 06:37:35PM -0400, Terry Lee Tucker wrote:
> On Saturday 10 June 2006 06:31 pm, "Yavuz Kavus" <yavuzkavus@gmail.com> thus
> communicated:
> --> i am writing a recursive procedure in pl/pgsql.
> --> i need to check whether a condition is true in any step of recursive
>  calls. -->
> --> if i get a true response in one call, i wont make other recursive calls
> --> anymore, because i get what i need.
> --> if no one gives me a true response, then uppest procedure will return
>  false --> as a result.
> -->
> --> i think i may achieve this with a static variable(shared among all
>  calls). -->
> --> is it possible?
> -->
> --> or another solution?
> -->
> --> thanks a lot for any help.
> -->
> I don't know of any static variable capability; however, I use various utility
> tables to accomplish this type of task. You can insert records into a table
> like this while inside a transaction and the current process will be the only
> process that can see it. When you are through with it, delete the record, all
> within the same transaction, and your purpose will be served.

If you use a temp table for that, you can have it truncate on
commit/rollback.
--
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461

In article <95b926fc0606101531q38cc2d6aq6c63730d4796f184@mail.gmail.com>,
Yavuz Kavus <yavuzkavus@gmail.com> wrote:

% i am writing a recursive procedure in pl/pgsql.
% i need to check whether a condition is true in any step of recursive calls.

[...]

% i think i may achieve this with a static variable(shared among all calls).

I'm not sure a static variable is the right way to achieve this, but
you could use a custom_variable_class for this. Add this to your
postgresql.conf:
 custom_variable_classes='global'

Then you can set and show variables prefixed by global.:
 set global.success = 'true';

--

Patrick TJ McPhee
North York  Canada
ptjm@interlog.com

Patrick TJ McPhee wrote:

>I'm not sure a static variable is the right way to achieve this, but
>you could use a custom_variable_class for this. Add this to your
>postgresql.conf:
> custom_variable_classes='global'
>
>Then you can set and show variables prefixed by global.:
> set global.success = 'true';
>
>
>
This is very intriguing, but I'd like to make sure it is doing what I
think it is doing.  Is it tracking variables in a connection across SQL
commands?  If so, shouldn't this work (Assume the existence of table
"detail" with a column called "val2").

create or replace function SetVal2() returns void as
$$
begin
    update detail set val2=global.val2;
end;
$$
language plpgsql

set global.val2=5;
select SetVal2();

I'm getting the following error which I don't understand.  Seems I don't
know how to access the variable once it is set.

ERROR:  missing FROM-clause entry for table "global"
CONTEXT:  SQL statement "update detail set val2=global.val2"
PL/pgSQL function "setval2" line 2 at SQL statement

Kenneth Downs wrote:

OK, cancel the question, the answer is

SELECT current_setting('global.val2');

> This is very intriguing, but I'd like to make sure it is doing what I
> think it is doing.  Is it tracking variables in a connection across
> SQL commands?  If so, shouldn't this work (Assume the existence of
> table "detail" with a column called "val2").
>
> create or replace function SetVal2() returns void as
> $$
> begin
>    update detail set val2=global.val2;
> end;
> $$
> language plpgsql
>
> set global.val2=5;
> select SetVal2();
>
> I'm getting the following error which I don't understand.  Seems I
> don't know how to access the variable once it is set.
>
> ERROR:  missing FROM-clause entry for table "global"
> CONTEXT:  SQL statement "update detail set val2=global.val2"
> PL/pgSQL function "setval2" line 2 at SQL statement
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: 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
>
>