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):
----------------------------------------------------------------
-- first do:
-- createdb test
-- createlang pltcl test
drop function my_lowercase() cascade;
create function my_lowercase() returns trigger as '
set NEW($1) lower(NEW($1))
return [array get NEW]' language 'pltcl';
drop table mytab;
create table mytab (myfield varchar);
create trigger trig_mytab before insert or update on mytab
for each row execute procedure my_lowercase('myfield');
-- let's insert a string, hope it's lowercase'd
insert into mytab (myfield) values ('TEST');
select * from mytab;
-- wrong, myfield contains 'lower(NEW(myfield))'
----------------------------------------------------------------
Can someone please tell me what I'm doing wrong? It's probably
something very simple but I don't know TCL (and I'm planning to keep
the serverside programming on pl'pgsql as much as possible).
TIA!