are there static variables in pgsql?

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');