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
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 |