help with TCL function

Op 10 Sep 2003 (15:27), schreef Jules Alberts <jules.alberts@arbodienst-limburg.nl>:

Hello everybody,

Someone helped me earlier with this TCL trigger function:

create or replace function tlow() returns trigger as '
set NEW($1) [string tolower $NEW($1)]
return [array get NEW]'
language 'pltcl';

I use it to force lowercase of values inserted in the db. There is one
problem though, if the parameter is absent, the function will give an
error message. I would like it to rather check for null before tolower
is applicated. In semicode:

if ($1 <> NULL) {
set NEW($1) [string tolower $NEW($1)]
}
return [array get NEW]'

but I have no idea how to do this in TCL. Can somebody please tell me?
Thanks a lot!

I found argisnull, but I can't run it, apparently it cannot be used in
triggers :-( Does anybody know a solutions for this problem? Thanks!

On Wednesday 10 September 2003 14:27, Jules Alberts wrote:

I use it to force lowercase of values inserted in the db. There is one
problem though, if the parameter is absent, the function will give an
error message. I would like it to rather check for null before tolower
is applicated. In semicode:

if ($1 <> NULL) {
set NEW($1) [string tolower $NEW($1)]
}
return [array get NEW]'

but I have no idea how to do this in TCL. Can somebody please tell me?
Thanks a lot!

According to the manuals:

$NEW

An associative array containing the values of the new table row for
INSERT/UPDATE actions, or empty for DELETE. The array is indexed by field
name. Fields that are NULL will not appear in the array!

Is that any use?

--
Richard Huxton
Archonet Ltd

jules.alberts@arbodienst-limburg.nl wrote:

Op 10 Sep 2003 (15:27), schreef Jules Alberts <jules.alberts@arbodienst-limburg.nl>:

Hello everybody,

Someone helped me earlier with this TCL trigger function:

create or replace function tlow() returns trigger as '
set NEW($1) [string tolower $NEW($1)]
return [array get NEW]'
language 'pltcl';

I use it to force lowercase of values inserted in the db. There is one
problem though, if the parameter is absent, the function will give an
error message. I would like it to rather check for null before tolower
is applicated. In semicode:

if ($1 <> NULL) {
set NEW($1) [string tolower $NEW($1)]
}
return [array get NEW]'

but I have no idea how to do this in TCL. Can somebody please tell me?
Thanks a lot!

I found argisnull, but I can't run it, apparently it cannot be used in
triggers :-( Does anybody know a solutions for this problem? Thanks!

I think you're missing something. When you use a Tcl function as a trigger,
any arguments (like $1) are explicitly supplied in the CREATE TRIGGER
command. You either define the trigger to call the function with a
constant argument or not; there is no issue of NULL here or an optional
argument. Show us your CREATE TRIGGER command.

Op 11 Sep 2003 (0:45), schreef ljb <lbayuk@mindspring.com>:

I think you're missing something. When you use a Tcl function as a
trigger, any arguments (like $1) are explicitly supplied in the CREATE
TRIGGER command. You either define the trigger to call the function with
a constant argument or not; there is no issue of NULL here or an
optional argument. Show us your CREATE TRIGGER command.

-- This is the function

create or replace function tlow() returns trigger as '
set NEW($1) [string tolower $NEW($1)]
return [array get NEW]'
language 'pltcl';

-- Now the table on which I use it

create table bedrijf (
code varchar unique not null check (code <> ''),
kvk_nummer varchar,
) with oids;

-- And two triggers

create trigger tlow
before insert or update
on bedrijf for each row
execute procedure tlow('code');

create trigger tlowkvk_nummer
before insert or update
on bedrijf for each row
execute procedure tlow('kvk_nummer');

--

As you can see, the column kvk_nummer is allowed to be null. But /if/
it gets a value, that value should be converted to lowercase. So a sql
statement like this should be allowed:

insert into bedrijf (code) values ('FUBAR');

However this will fail because the trigger tlowkvk_nummer will call
tlow() without an argument. So I want to program tlow() defensively,
after all my DB logic should work, independenlty of anything that goes
on in the frontend.

I think it's a valid thing to expect from a DB or programming language.
Workarounds could be traversing all the frontend code to make sure no
NULL values are used or not using this kind of constraint at all. Not
really options IMO.

Thanks for any help!

Am Donnerstag, 11. September 2003 08:59 schrieben Sie:

Op 11 Sep 2003 (0:45), schreef ljb <lbayuk@mindspring.com>:

...

-- This is the function

create or replace function tlow() returns trigger as '
set NEW($1) [string tolower $NEW($1)]
return [array get NEW]'
language 'pltcl';

...

You could either test with info exists or catch the error
create or replace function tlow() returns trigger as '
if [info exists NEW($1)] {
set NEW($1) [string tolower $NEW($1)]
}
return [array get NEW]'
language 'pltcl';

create or replace function tlow() returns trigger as '
catch {
set NEW($1) [string tolower $NEW($1)]
}
return [array get NEW]'
language 'pltcl';

--
Rolf Jentsch
Produktentwicklung EDV-Anwendungen f�r Mitglieder
ElectronicPartner GmbH & Co. KG
D�sseldorf

Ok you can user the info exists function that is part of TCL

i.e. if { [info exists NEW($1)] } {

or if 1 is the problem do
if { [info exists 1] } {

This will allow you to determine if it exists.

If you want to see what variables exist at this level elog [info vars] and
it will show you all variables currently accessable

Also you can array names or array get the contents or the NEW array then
foreach id value (if using array get) you can put them lower.

HTH
Darren

On Thu, 11 Sep 2003, Jules Alberts wrote:

Op 11 Sep 2003 (0:45), schreef ljb <lbayuk@mindspring.com>:

I think you're missing something. When you use a Tcl function as a
trigger, any arguments (like $1) are explicitly supplied in the CREATE
TRIGGER command. You either define the trigger to call the function with
a constant argument or not; there is no issue of NULL here or an
optional argument. Show us your CREATE TRIGGER command.

-- This is the function

create or replace function tlow() returns trigger as '
set NEW($1) [string tolower $NEW($1)]
return [array get NEW]'
language 'pltcl';

-- Now the table on which I use it

create table bedrijf (
code varchar unique not null check (code <> ''),
kvk_nummer varchar,
) with oids;

-- And two triggers

create trigger tlow
before insert or update
on bedrijf for each row
execute procedure tlow('code');

create trigger tlowkvk_nummer
before insert or update
on bedrijf for each row
execute procedure tlow('kvk_nummer');

--

As you can see, the column kvk_nummer is allowed to be null. But /if/
it gets a value, that value should be converted to lowercase. So a sql
statement like this should be allowed:

insert into bedrijf (code) values ('FUBAR');

However this will fail because the trigger tlowkvk_nummer will call
tlow() without an argument. So I want to program tlow() defensively,
after all my DB logic should work, independenlty of anything that goes
on in the frontend.

I think it's a valid thing to expect from a DB or programming language.
Workarounds could be traversing all the frontend code to make sure no
NULL values are used or not using this kind of constraint at all. Not
really options IMO.

Thanks for any help!

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html

--
Darren Ferguson