pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Frank Joerdens <frank@joerdens.de>
To: pgsql-sql@postgresql.org
Subject: Rule trouble (looks to me exactly like the example)
Date: Thu, 4 Apr 2002 19:17:19 +0200
Message-ID: <20020404191719.C30469@superfly.archi-me-des.de> (raw)

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



view thread (6+ messages)  latest in thread

Message-ID: <20020404191719.C30469@superfly.archi-me-des.de>
Permalink:  ../20020404191719.C30469@superfly.archi-me-des.de/
Also on:    postgresql.org/message-id/20020404191719.C30469@superfly.archi-me-des.de

 · 

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 using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: frank@joerdens.de
  Subject: Re: Rule trouble (looks to me exactly like the example)
  In-Reply-To: <20020404191719.C30469@superfly.archi-me-des.de>

* 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