PL/SQL & unset custom variable

Gordon Ross <gr306@ucs.cam.ac.uk> wrote:

(I'm using Postgres 8.3)

I have a trigger that references a custom variable. Most of the time this
custom variable is set, and I have no problems.

However, in certain corner cases the custom variable is not set and the
trigger fails.

Is there a way to either test if the custom variable is set, or to specify a
global default for the custom variable ?

I think, you can use COALESCE(your_variable, default_value) to solve
that problem. Try it, it is untested.

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

On 02/09/2009 15:39, "Andreas Kretschmer" <akretschmer@spamfence.net> wrote:

Gordon Ross <gr306@ucs.cam.ac.uk> wrote:

Is there a way to either test if the custom variable is set, or to specify a
global default for the custom variable ?

I think, you can use COALESCE(your_variable, default_value) to solve
that problem. Try it, it is untested.

Sorry, no joy :-(

grails=> SELECT current_setting('phone.id');
ERROR: unrecognized configuration parameter "phone.id"

grails=> SELECT coalesce(current_setting('phone.id'),'SYSTEM');
ERROR: unrecognized configuration parameter "phone.id"

GTG

Gordon Ross <gr306@ucs.cam.ac.uk> writes:

Sorry, no joy :-(

grails=> SELECT current_setting('phone.id');
ERROR: unrecognized configuration parameter "phone.id"

grails=> SELECT coalesce(current_setting('phone.id'),'SYSTEM');
ERROR: unrecognized configuration parameter "phone.id"

You could use a SELECT from pg_settings to probe for the value
of the parameter without getting an error.

regards, tom lane

Hmmm.

If I do:

select * from pg_settings where name='custom_variable_classes';

I see my entry "phone", but I can't see how I can tell if I've set "phone.id"

GTG
________________________________________
From: Tom Lane [tgl@sss.pgh.pa.us]
Sent: 02 September 2009 19:16
To: Gordon Ross
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PL/SQL & unset custom variable

Gordon Ross <gr306@ucs.cam.ac.uk> writes:

Sorry, no joy :-(

grails=> SELECT current_setting('phone.id');
ERROR: unrecognized configuration parameter "phone.id"

grails=> SELECT coalesce(current_setting('phone.id'),'SYSTEM');
ERROR: unrecognized configuration parameter "phone.id"

You could use a SELECT from pg_settings to probe for the value
of the parameter without getting an error.

regards, tom lane