Received: from sardinia.gssec.bt.co.uk (sardinia.gssec.bt.co.uk [132.146.39.103]) by hub.org (8.9.2/8.9.1) with ESMTP id HAA96738 for ; Thu, 11 Feb 1999 07:35:08 -0500 (EST) (envelope-from maillist@remo.demon.co.uk) Received: from dhcpw198.gssec.bt.co.uk ([132.146.203.198]:4288 "HELO towhee" ident: "IDENT-NOT-QUERIED") by sardinia.gssec.bt.co.uk with SMTP id <936989-332>; Thu, 11 Feb 1999 12:26:51 +0000 From: "Neil Burrows" To: "Jan Wieck" , "Neil Burrows" Cc: Subject: RE: [SQL] RULE questions. Date: Thu, 11 Feb 1999 12:34:12 -0000 Message-ID: <000d01be55ba$d43b32c0$c6cb9284@towhee.gssec.bt.co.uk> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 (Normal) X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook 8.5, Build 4.71.2173.0 In-reply-to: Importance: Normal X-MimeOLE: Produced By Microsoft MimeOLE V4.72.2106.4 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. > 1. Make sure user_id is unique or extend the WHERE clause in > the UPDATE rule. To explain why: This is actually just a small test table, and the real one has quite a few more columns, but I did mean to make user_id unique, just forgot. :) > 2. Change the WHERE clause in the UPDATE rule to compare > against old.user_id and add "user_id = new.user_id" to > the SET clause. Otherwise it would not be possible to > change the user_id because this thrown away by the rule. The thinking behind it was that user_id shouldn't be able changed but I accidentally neglected to mention that. > > 2) Users can still enter data straight into test_table in lower case > > bypassing the "rules" > Not necessarily. Since v6.4 rule actions (in contrast to > triggers up to now) inherit the access permissions of the > owner of the relation they're fired on. Ahh, I see. I thought that the rule actions used the current users access permissions, not the owners. That's much handier, thanks. > In addition to that, consider the case you really don't want > once given user_id's ever to change. Nor you like them to be > ever reused. But they should disappear on DELETE. > > CREATE TABLE test_table (user_id int, > name varchar(10), > pass varchar(10), > alive bool); > And that's a great way of doing what I was going to start looking at next. :) > The Postgres rewrite rule system is the most powerful way to > do that. Thanks very much for your time and comments here. It's certainly made things clearer. Thanks again, ---[ 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 >-----------