Referencing any field in a trigger

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