pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Neil Burrows <maillist@remo.demon.co.uk>
To: Jan Wieck <jwieck@debis.com>
To: Neil Burrows <maillist@remo.demon.co.uk>
Cc: pgsql-sql@postgreSQL.org
Subject: RE: [SQL] RULE questions.
Date: Thu, 11 Feb 1999 12:34:12 -0000
Message-ID: <000d01be55ba$d43b32c0$c6cb9284@towhee.gssec.bt.co.uk> (raw)
In-Reply-To: <m10Auzx-000EBRC@orion.SAPserv.Hamburg.dsh.de>

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




view thread (10+ messages)  latest in thread

Message-ID: <000d01be55ba$d43b32c0$c6cb9284@towhee.gssec.bt.co.uk>
Permalink:  ../000d01be55ba$d43b32c0$c6cb9284@towhee.gssec.bt.co.uk/
Also on:    postgresql.org/message-id/000d01be55ba$d43b32c0$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: maillist@remo.demon.co.uk, jwieck@debis.com
  Subject: Re: [SQL] RULE questions.
  In-Reply-To: <000d01be55ba$d43b32c0$c6cb9284@towhee.gssec.bt.co.uk>
  References: <m10Auzx-000EBRC@orion.SAPserv.Hamburg.dsh.de> <000d01be55ba$d43b32c0$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