pl/tcl trigger question

[Jules Alberts]

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!

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[Robert Treat]

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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

[darren]

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
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

[Jules Alberts]

Op 26 Aug 2003 (12:38), schreef Robert Treat <xz****@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.

Thanks again for any help, and sorry if I'm asking basic TCL questions,
I don't know the language. Do you happen to know a good site where the
language is explained? All I googled was about creating widgets, GUI
stuff :-(
Robert Treat

---------------------------(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

[Jan Wieck]

Jules Alberts wrote:

Op 26 Aug 2003 (12:38), schreef Robert Treat <xz****@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. #
#================================================= = Ja******@Yahoo.com #
---------------------------(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