On Wed, Feb 8, 2012 at 10:51 AM, Andreas Kretschmer <akretschmer@xxxxxxxxxxxxx> wrote: > mgould@xxxxxxxxxxxxxxxxxxxx <mgould@xxxxxxxxxxxxxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
