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('m yfield');

-- let's insert a string, hope it's lowercase'd
insert into mytab (myfield) values ('TEST');
select * from mytab;

-- wrong, myfield contains 'lower(NEW(myfi eld))'
----------------------------------------------------------------

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 YourEmailAddres sHere" to majordomo@postg resql.org)

[Robert Treat]

Re: pl/tcl trigger question

On Tue, 2003-08-26 at 07:28, Jules Alberts wrote:[color=blue]
> 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('m yfield');
>
> -- let's insert a string, hope it's lowercase'd
> insert into mytab (myfield) values ('TEST');
> select * from mytab;
>
> -- wrong, myfield contains 'lower(NEW(myfi eld))'
> ----------------------------------------------------------------
>
> 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).
>[/color]

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 YourEmailAddres sHere" to majordomo@postg resql.org)

[darren@crystalballinc.com]

Re: pl/tcl trigger question

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:
[color=blue]
> On Tue, 2003-08-26 at 07:28, Jules Alberts wrote:[color=green]
> > 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('m yfield');
> >
> > -- let's insert a string, hope it's lowercase'd
> > insert into mytab (myfield) values ('TEST');
> > select * from mytab;
> >
> > -- wrong, myfield contains 'lower(NEW(myfi eld))'
> > ----------------------------------------------------------------
> >
> > 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).
> >[/color]
>
> 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
>[/color]

--
Darren Ferguson


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postg resql.org

[Jules Alberts]

Re: pl/tcl trigger question

Op 26 Aug 2003 (12:38), schreef Robert Treat <xzilla@users.s ourceforge.net> :[color=blue]
> On Tue, 2003-08-26 at 07:28, Jules Alberts wrote:[color=green]
> > 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):[/color][/color]

<bad attempt snipped>
[color=blue]
> 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.[/color]

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 :-(
[color=blue]
> Robert Treat[/color]

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

Re: pl/tcl trigger question



Jules Alberts wrote:
[color=blue]
> Op 26 Aug 2003 (12:38), schreef Robert Treat <xzilla@users.s ourceforge.net> :[color=green]
>> On Tue, 2003-08-26 at 07:28, Jules Alberts wrote:[color=darkred]
>> > 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):[/color][/color]
>
> <bad attempt snipped>
>[color=green]
>> 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.[/color]
>
> 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.[/color]

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('fi eld_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. #
#============== =============== =============== ====== JanWieck@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