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