From: | Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> |
---|---|
To: | Jules Alberts <jules(dot)alberts(at)arbodienst-limburg(dot)nl> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: pl/tcl trigger question |
Date: | 2003-08-26 16:38:51 |
Message-ID: | 1061915931.4596.351.camel@camel |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
From | Date | Subject | |
---|---|---|---|
Next Message | javier garcia - CEBAS | 2003-08-26 16:40:12 | Fwd: installing postgres7.3.4 problem II |
Previous Message | andy morrow | 2003-08-26 16:16:38 |