Hi,
I have what I first thought would be a trivial problem, in that I require
the 2 VARCHAR columns in the following table to have the data stored in
upper case.
test_table
+-----------------------------+------------------------------+------+
| Field | Type |Length|
+-----------------------------+------------------------------+------+
| user_id | int4 | 4 |
| name | varchar() | 10 |
| password | varchar() | 10 |
+-----------------------------+------------------------------+------+
I considered just using UPPER() in every SELECT statement, or creating a
view that SELECTed the 2 VARCHAR cols using UPPER but I assume that this
would have more performance issues than having the data converted to
uppercase only once during an insert or update. (Please correct me if I am
wrong).
After looking at triggers and rules I came up with the following solution:
CREATE VIEW test AS SELECT * FROM test_table;
CREATE RULE insert_test AS
ON INSERT TO test DO INSTEAD
INSERT INTO test_table (user_id, name, password) VALUES(new.user_id,
UPPER(new.name), UPPER(new.password));
CREATE RULE update_test AS
ON UPDATE TO test DO INSTEAD
UPDATE test_table SET name = UPPER(new.name), password = UPPER(new.password)
WHERE user_id = new.user_id;
which means that any insert or update to the test view is stored in upper
case as required.
However, I still have two concerns about this.
1) What impact on performance does using a VIEW in this way have?
2) Users can still enter data straight into test_table in lower case
bypassing the "rules"
First off, is there an easier way to ensure that data is stored in uppercase
for certain columns (not the whole table). And if not does anyone have
comments on performance issues, or ways of stopping users accidentally or
intentionally inserting lower case data straight into the table rather than
the view?
Many thanks in advance,
---[ Neil Burrows ]-----------------------------------------------------
E-mail: neil.burrows@gssec.bt.co.uk British Telecom Plc.
: neil@pawprint.co.uk Glasgow Engineering Centre
Web : http://www.remo.demon.co.uk/ Highburgh Rd. Glasgow UK
-----------< Any views expressed are not those of my employer >-----------
