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

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?

usually I like to explain why it's not possible before giving the game
away, but I see that others have already explained that.

here's a trigger function that should do what you want.

create or replace function upper_row() returns trigger language plpgsql as
$$
begin -- I consider this a hack. no warranty express or implied
execute 'select ('|| quote_literal(upper(new::text))
||'::'|| quote_ident(TG_TABLE_SCHEMA)
||'.'|| quote_ident(TG_TABLE_NAME) || ').*'
into new;
return new;
end;
$$;

what it does is convert new into a string
and then uppercase the string
then convert the string back into a record
and put the result back into new.

I have tested it with ASCII text and it seems to work fine,
any datatypes which are case sensitive will be effected
numbers and timestamps should be unaffected, but note that
this trigger will mangle BYTEA data.

because it uses execute it's not particularly efficient should you do
any bulk updates, other that that the overhead should not be too much.

--
⚂⚃ 100% natural