Ok the way you could do this is as follows:
create or replace function my_lowercase() returns trigger as '
foreach id [array names NEW] {
set NEW($id) [string tolower $NEW($id)]
}
return [array get NEW]
' language 'pltcl';
HTH
Darren
On 26 Aug 2003, Robert Treat wrote:
> 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):
> >
> > ----------------------------------------------------------------
> > -- 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).
> >
>
> 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.
>
> Robert Treat
>
--
Darren Ferguson
