Creating trigger functions in pl/pgsql

From: John Cochran <jdc(at)fiawol(dot)org>
To: pgsql-general(at)postgresql(dot)org
Subject: Creating trigger functions in pl/pgsql
Date: 2002-12-17 22:33:34
Message-ID: 200212172233.gBHMXZLS030217@smof.fiawol.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Greetings,

I'm having a bit of a problem with a PL/pgSQL function that I would like
to write. I have a table where I would like to force every entry in a
certain column to be lower case. This can be easily done with a simple
PL/pgSQl function such as

create function forcelower() returns opaque as '
begin
new.column_name = lower(new.column_name);
return new;
end' language 'plpgsql';

with a trigger of

create trigger force_to_lower before insert or update
on table_name for each row
execute procedure forcelower();

The above is quite simple. But forcing a column to become lowercase
is a common enough operation that I'm wondering if it would be possible
to create a function that receives as it's parameters (accessed vi tg_argv[])
the column name of the table to force to lower case. If this is possible,
then the following trigger creations would be possible.

create trigger force_to_lower_table1 before insert or update
on table_1 for each row
execute procedure forcelower('column_name');

and on another table you could use

create trigger force_to_lower_table2 before insert or update
on table_2 for each row
execute procedure forcelower('a_different_column_name');

So the problem boils down to,
Is there any way to access/modify the values within "new" when you
have a variable containing the name of the column who's value you wish
to access or modify?
Something along the lines of

declare
name text;
begin
name = 'column_name';
new.name = lower(new.name);
return new;
end;

where the column being modified has the name of 'column_name' instead of
the name 'name'?

Thank you for your time,
John Cochran

Browse pgsql-general by date

  From Date Subject
Next Message Doug Fields 2002-12-17 22:47:13 Problem with VACUUM after very large delete?
Previous Message Feite Brekeveld 2002-12-17 21:25:30 Re: extending by using procedurallanguage C : problems