Thread: Referencing any field in a trigger
How can I reference any NEW field in an insert/update trigger function? When someone inserts or updates any field with a single asterisk (*), I need it to become '%%%'. But if they use an asterisk in any combination with other fields, then I want to TRANSLATE those asterisks to a single '%'. I was hoping not to have to test every field in the table. -- Robert
On Fri, 2007-05-25 at 11:17 -0400, Robert Fitzpatrick wrote: > But if they use an asterisk in any combination > with other fields I meant to say 'But if they use an asterisk in any combination with other *values* in the field...'. For instance, if they enter '*test*', it will be TRANSLATE'd to '%test%'. -- Robert
Robert Fitzpatrick wrote: > How can I reference any NEW field in an insert/update trigger function? > When someone inserts or updates any field with a single asterisk (*), I > need it to become '%%%'. But if they use an asterisk in any combination > with other fields, then I want to TRANSLATE those asterisks to a single > '%'. You can't do that with PL/pgSQL. You can with other languages like PL/Perl though (which has better tools for string treatment, so it is a good idea anyway). -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
On Fri, 2007-05-25 at 12:00 -0400, Alvaro Herrera wrote: > Robert Fitzpatrick wrote: > > How can I reference any NEW field in an insert/update trigger function? > > When someone inserts or updates any field with a single asterisk (*), I > > need it to become '%%%'. But if they use an asterisk in any combination > > with other fields, then I want to TRANSLATE those asterisks to a single > > '%'. > > You can't do that with PL/pgSQL. You can with other languages like > PL/Perl though (which has better tools for string treatment, so it is a > good idea anyway). Great! I use Perl for a lot of string functions now, but I've never used pl/perl for triggers. I did not know I could use pl/perl in triggers...but do now :) http://www.postgresql.org/docs/8.0/static/plperl-triggers.html But still, how would I reference all fields using the pl/perl? Can I specify column numbers versus names as in '$_TD->{new}{1}' for the first column and loop or something? For instance, I would like to be able to say if any NEW column has a single asterisk only, set it to '%%%'. -- Robert
Robert Fitzpatrick wrote:
> But still, how would I reference all fields using the pl/perl? Can I
> specify column numbers versus names as in '$_TD->{new}{1}' for the first
> column and loop or something? For instance, I would like to be able to
> say if any NEW column has a single asterisk only, set it to '%%%'.
Well, do a foreach ($_TD->{new}) or foreach (keys $_TD->{new}) (not sure
of the exact syntax but if you're used to Perl you can figure it out).
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
On Fri, 2007-05-25 at 13:45 -0400, Alvaro Herrera wrote:
> Robert Fitzpatrick wrote:
>
> > But still, how would I reference all fields using the pl/perl? Can I
> > specify column numbers versus names as in '$_TD->{new}{1}' for the first
> > column and loop or something? For instance, I would like to be able to
> > say if any NEW column has a single asterisk only, set it to '%%%'.
>
> Well, do a foreach ($_TD->{new}) or foreach (keys $_TD->{new}) (not sure
> of the exact syntax but if you're used to Perl you can figure it out).
>
You gotta love pgsql, took a bit, but done...
foreach $i (keys %{$_TD->{new}}) {
if (${$_TD->{new}}{$i} eq '*') {
${$_TD->{new}}{$i} = '%%%';
} else {
${$_TD->{new}}{$i} =~ s/\*/\%/g;
}
}
return "MODIFY";
--
Robert