Thread: parameter in trigger function

Hello everyone,

Several columns in sereval tables in my DB should always be lowercase.
I now have a simple function:

create or replace function code_lower() returns trigger as '
begin
    NEW.code := lower(NEW.code);
    return NEW;
end'
language 'plpgsql';

which I call with a trigger like this:

create trigger my_trigger
    before insert or update on my_table
    execute procedure code_lower();

This will successfully lower() a field named 'code' in the table
'mytable' or any other table to which I point it. But some of my tables
have fields which should be lower()ed that have names other than
'code'. Writing a function for every of these field seems stupid, so I
tried to give the trigger arguments. Code like this

NEW.$1 := lower(NEW.$1)

won't work, all I get is error messages :-( The doc says this should be
OK (http://www.postgresql.org/docs/7.3/static/triggers.html) but Google
mostly says the opposite. Is this possible at all? How do I read the
TriggerData structure from whithin a pl/pgsql function?

TIA!

"Jules Alberts" <jules.alberts@arbodienst-limburg.nl> writes:
> Code like this
> NEW.$1 := lower(NEW.$1)
> won't work, all I get is error messages :-(

Can't do that in plpgsql; it does not like run-time specification of
table or field names.  You could try using EXECUTE but I doubt that
will work either for an assignment to NEW.

I'd suggest using pltcl, which is more amenable to run-time
determination of field names.

> mostly says the opposite. Is this possible at all? How do I read the
> TriggerData structure from whithin a pl/pgsql function?

You are looking for TG_ARGV[], though this is not your biggest
problem...

            regards, tom lane

Jules Alberts wrote:
> Hello everyone,
>
> Several columns in sereval tables in my DB should always be lowercase.
> I now have a simple function:
>
> create or replace function code_lower() returns trigger as '
> begin
>     NEW.code := lower(NEW.code);
>     return NEW;
> end'
> language 'plpgsql';
>
> which I call with a trigger like this:
>
> create trigger my_trigger
>     before insert or update on my_table
>     execute procedure code_lower();
>
> This will successfully lower() a field named 'code' in the table
> 'mytable' or any other table to which I point it. But some of my tables
> have fields which should be lower()ed that have names other than
> 'code'. Writing a function for every of these field seems stupid, so I
> tried to give the trigger arguments. Code like this
>
> NEW.$1 := lower(NEW.$1)
>
> won't work, all I get is error messages :-( The doc says this should be
> OK (http://www.postgresql.org/docs/7.3/static/triggers.html) but Google
> mostly says the opposite. Is this possible at all? How do I read the
> TriggerData structure from whithin a pl/pgsql function?

It might seem stupid on the first look, but let's look again. PL/pgSQL
is a language that makes heavy use of cached query plans. Let's assume
your sample function above is triggered on it's first call in a session
for a table where "code" is an attribute of type "text". It will prepare
an SPI plan with the query "SELECT lower($1)" and tell the parser and
planner that the parameter $1 is of type "text". This plan is saved and
never touched again during the lifetime of your connection.

Now whenever your trigger function is called, it will put NEW.code into
a Datum array and call SPI_execp() for the above prepared plan. If you
now install the same trigger function on a table where "code" is of type
"name", this will not work because of a parameter type mismatch.

So it's even better not only to create separate functions per field
name, it's best to create a separate function for every single trigger.

You can alternatively use a language with fine control over SPI plan
caching like PL/Tcl. But then you loose exactly that optimization and
your trigger has to parse and plan this "SELECT lower('quotedval')" on
every single invocation. Do that only if you are sure to have ample
spare cpu cycles.


Jan

--
#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #