Re: Trigger to force fields to lowercase at insert/update
От | Andreas Kretschmer |
---|---|
Тема | Re: Trigger to force fields to lowercase at insert/update |
Дата | |
Msg-id | 20060429201708.GA25517@KanotixBox обсуждение исходный текст |
Ответ на | Trigger to force fields to lowercase at insert/update (Leonardo Boiko <leoboiko@gmail.com>) |
Список | pgsql-novice |
Leonardo Boiko <leoboiko@gmail.com> schrieb: > Suppose I have a bunch of fields in different tables which I'd like to > always force to lowercase (since they represent case-insensitive data). > Suppose I want to do it at SQL level, not application level. If the > user say "INSERT INTO foo VALUES ('Abc')", I want to really insert insert into foo values (lower('Abc')); ? > 'abc'. Is there an elegant way of doing this? Some kind of trigger, > maybe? I have no experience with triggers or pl/sql, but I couldn't test=# create table foo (x text); CREATE TABLE test=# create function foo_lower() returns trigger as $$begin NEW.x := lower(NEW.x); return NEW; end;$$ language plpgsql; CREATE FUNCTION test=# create trigger t_foo before insert or update on foo for each row execute procedure foo_lower(); CREATE TRIGGER test=# insert into foo (x) values ('AbC'); INSERT 0 1 test=# select * from foo; x ----- abc (1 row) HTH, 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." (unknow) Kaufbach, Saxony, Germany, Europe. N 51.05082°, E 13.56889°
В списке pgsql-novice по дате отправления: