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

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