Jules Alberts wrote:
Op 26 Aug 2003 (12:38), schreef Robert Treat <[EMAIL PROTECTED]>: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. # #================================================== [EMAIL PROTECTED] #
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
