pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Neil Burrows <neil@remo.demon.co.uk>
To: pgsql-sql@postgresql.org
Subject: RULE questions.
Date: Thu, 11 Feb 1999 09:12:03 -0000
Message-ID: <000101be559e$96b4d620$c6cb9284@towhee.gssec.bt.co.uk> (raw)

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




view thread (10+ messages)  latest in thread

Message-ID: <000101be559e$96b4d620$c6cb9284@towhee.gssec.bt.co.uk>
Permalink:  ../000101be559e$96b4d620$c6cb9284@towhee.gssec.bt.co.uk/
Also on:    postgresql.org/message-id/000101be559e$96b4d620$c6cb9284@towhee.gssec.bt.co.uk

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: neil@remo.demon.co.uk
  Subject: Re: RULE questions.
  In-Reply-To: <000101be559e$96b4d620$c6cb9284@towhee.gssec.bt.co.uk>
  References: <000101be559e$96b4d620$c6cb9284@towhee.gssec.bt.co.uk>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox