Re: pl/tcl trigger question

Jules Alberts wrote:

> Op 26 Aug 2003 (12:38), schreef Robert Treat <xzilla@users.sourceforge.net>:
>> On Tue, 2003-08-26 at 07:28, Jules Alberts wrote:
>> > Hello everyone,
>> >
>> > I'm working on a tiny trigger function that needs to ensure that all
>> > values entered in a field are lowercase'd. I can't use pl/pgsql
>> > because I have a dozen different columns (with different names) that
>> > need a trigger that does this and pl'pgsql can't expand variable names
>> > to fieldnames. Writing a dozen functions (one per columnname) is /way/
>> > too blunt so I tried pl/tcl (which I don't know):
>
> <bad attempt snipped>
>
>> You'll need a function a bit more complex than this, but to do what your
>> trying to do in the function above the function would be written as:
>>
>> create or replace function my_lowercase() returns trigger as '
>>   set NEW($1) [string tolower $NEW($1)]
>>   return [array get NEW]' language 'pltcl';
>>
>> Hope this helps, please post the final results when you get there.
>
> Hi Robert,
>
> It works great, thanks a lot! There is one little issue though: when I
> insert null values, the function fails. I think I can work around this
> by giving the columns a default value of '' in my table design, but I
> would like a more defensive approach, I.E. having my_lowercase() check
> for null values.

Have you tried the scriptics site http://www.scriptics.com/ under
"web-resources->documentation"? There are some tutorials and howto's.


create or replace function force_lower () returns trigger as '
     foreach key $args {
         if {[info exists NEW($key)]} {
             set NEW($key) [string tolower $NEW($key)]
         }
     }
     return [array get NEW]
' language pltcl;

create trigger force_lower before insert or update on mytable
     for each row execute procedure force_lower('field_1', 'field_n');


This works for a variable number of fields on every table and ignores
NULL values.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #