Rule trouble (looks to me exactly like the example)

From: Frank Joerdens <frank(at)joerdens(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: Rule trouble (looks to me exactly like the example)
Date: 2002-04-04 17:17:19
Message-ID: 20020404191719.C30469@superfly.archi-me-des.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

This is my table:

CREATE TABLE tree (
id serial PRIMARY KEY,
parentid int4 NOT NULL REFERENCES tree ( id ),
ordering int4 NOT NULL,
leveling int4 NOT NULL,
status int4 NOT NULL,
title text NOT NULL
);

And here's the rule (it doesn't do anything real or interesting, I just
simplified it to the point where it looked like the example):

CREATE RULE insert_level AS
ON INSERT TO tree
WHERE new.id > 0 DO
UPDATE tree SET leveling = 1
WHERE tree.oid = new.oid;

which looks to me like one of the examples in Bruce's book (page 299 in
my pdf version):

CREATE RULE example_5 AS
ON INERT TO emp
WHERE new.salary > 5000 DO
UPDATE emp SET salary = 5000
WHERE emp.oid = new.oid;

If I do an insert like

INSERT INTO tree (parentid,ordering,leveling,status,title)
VALUES (0,0,0,1,'foo');

nothing happens to the leveling column though:

archiweb=# select * from tree ;
id | parentid | ordering | leveling | status | title
----+----------+----------+----------+--------+-----------
0 | 0 | 0 | 0 | 0 | root node
11 | 0 | 0 | 0 | 1 | foo
12 | 0 | 0 | 0 | 1 | foo
(3 rows)

Although the rule definitely is there:

archiweb=# select tablename,rulename from pg_rules;
tablename | rulename
-----------+--------------
tree | insert_level
(1 row)

(the definition column looks ok too, I didn't include it because it'd
look all messed up in the email here because it's in a single line.)

I am using Postgres version 7.2.1 on Linux.

Can anyone explain that?

Regards, Frank

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Josh Berkus 2002-04-04 17:20:05 Re: 16 parameter limit
Previous Message Tom Lane 2002-04-04 17:09:08 Re: md5 auth