Re: easy function or trigger to UPPER() all alpha data

On Wed, Feb 8, 2012 at 10:51 AM, Andreas Kretschmer
<akretschmer(at)spamfence(dot)net> wrote:
> mgould(at)isstrucksoftware(dot)net <mgould(at)isstrucksoftware(dot)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