On Wed, Feb 8, 2012 at 10:51 AM, Andreas Kretschmer
<akretschmer@spamfence.net> wrote:
> mgould@isstrucksoftware.net <mgould@isstrucksoftware.net> wrote:
>
>> We need to ensure that our data is in upper case only in the db. Is there a
>> easy way to do this via a function without having to name each column
>> separately?
>
> You can define a TRIGGER for such tasks (befor insert or update), but
> you have to name each column (maybe not within triggers written in
> pl/perl, i'm not sure ...)
you can skirt the restriction with some hstore (ab)use...
create or replace function all_upper() returns trigger as
$$
begin
new := populate_record(new, hstore(array_agg(key),
array_agg(upper(value)))) from each(hstore(new));
return new;
end;
$$ language plpgsql;
create trigger on_foo_insert before insert on foo
for each row execute procedure all_upper();
postgres=# insert into foo values (1, 'abc', 'def');
INSERT 0 1
Time: 3.388 ms
postgres=# select * from foo;
a | b | c
---+-----+-----
1 | ABC | DEF
(1 row)
of course, if some non text datatype is sensitive to case in it's
textual formatting, this might break.
merlin