Re: Synchronising multiple common fields among tables

On 03/07/2003 06:28, Bruno Wolff III wrote:
> On Wed, Jul 02, 2003 at 23:18:47 +0100,
>   Dominic Marks <dom@cus.org.uk> wrote:
> >
> > I had a number of ideas about how to implement this, one was
> > using a separate VIEW for each services table. However I
> > have discovered that PostgreSQL only supports read-only views
> > at the current time.
>
> You can use rules to make updatable views.

Firstly, Thank you to all those who gave me feedback and
suggestions.

I have tried writing some rules and it seems to work fairly well.
So far I have setup three rules for each service table which are
attached to the central users table, handling all the available
actions.

The only problem difficulty I have is avoiding circular rules
when I try and get the information to flow in both directions.
For example, an INSERT to the users table correctly creates valid
records in my samba and postfix user tables - but when I add a
rule to update the users table when an UPDATE occurs in the samba
users table I get the expected errors with circular rules. I've
thought of a number of ways which I might be able to work around
this, my first approach was:

CREATE RULE samba_users_update AS
  ON UPDATE TO samba_users DO
  UPDATE users
    SET password = new.password
    WHERE old.password <> new.password;

However this does not work. My next idea is to use a SEQUENCE
and prevent the cycle by checking if an additional field in the
table has been incremented at least once. I will try this tomorrow.

Also, does anyone know at what priviledge level the rules execute
at ? I need them to be able to operate at the administrator level
so that rewrites on queries submitted by users which don't have
priviledges on the users table can still be processed.

If anyone has any further suggestions I'd be glad to hear them.

Thanks,
--
Dominic
 <dom at cus.org.uk> <dominic.marks at npl.co.uk>