Обсуждение: help with TCL function
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!
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