easy function or trigger to UPPER() all alpha data

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 ...)

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

mgould@isstrucksoftware.net <mgould@isstrucksoftware.net> wrote:

Andres,

Darn, I was hoping to not need to do one for each table. I was hoping that
using %RowType might work.

1st, please answer to the list, not to me, okay?
2nd, please don't top-posting, quote below. It's hard to read
3rd, i understand your problem, but afaik, you have to name all
fields and you can't interate to NEW and you can't grag the
column-names.

As i said, maybe with pl/perl, i'm not sure.

(and 4th, don't use images in your mail, in particular within
mailinglists)

Regards,

Andreas
--
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect. (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly." (unknown)
Kaufbach, Saxony, Germany, Europe. N 51.05082�, E 13.56889�

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?

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

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

On Mon, Feb 13, 2012 at 11:42 AM, <mgould@isstrucksoftware.net> wrote:

Thank you very much.  This is most helpful.

you're welcome. Keep in mind hstore features you need start with
postgres 9.0 and it's an extension aka contrib you have to add to the
database. (also as Andreas noted, please try to keep responses
on-list).

merlin