Skip site navigation (1) Skip section navigation (2)

Re: rules or trigers?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Marcin Mazurek <M(dot)Mazurek(at)poznan(dot)multinet(dot)pl>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: rules or trigers?
Date: 2000-08-30 21:12:18
Message-ID: 27214.967669938@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-general
Marcin Mazurek <M(dot)Mazurek(at)poznan(dot)multinet(dot)pl> writes:
> Simple example to make things clearer.
> CREATE TABLE tab (id INT SERIAL PRIMARY KEY, sth TEXT); --main table
> CREATE TABLE log_tab(id INT, sth TEXT);	--table to maintain logs in it

> CREATE RULE tab_log_ins AS ON INSERT TO tab DO
>         INSERT INTO log_tab  VALUES (new.id, new.sth);

> INSERT INTO tab (sth) VALUES ('something');
> when I insert new raw in tab, id field differs (rises by one) from id in
> log_tab, how can i avoid it?

At least at the moment, the only way is to use a trigger.

The problem is this.  Your insert is transformed by the parser to include
the defaults for the missing columns:

INSERT INTO tab (id, sth) VALUES (nextval('id_seq'), 'something');

Then the rule is applied.  That's also fundamentally a textual
transformation, so what actually gets executed is equivalent to

INSERT INTO log_tab  VALUES (nextval('id_seq'), 'something');
INSERT INTO tab (id, sth) VALUES (nextval('id_seq'), 'something');

See the problem?  What you want is to lay your hands on the actual
values that are getting inserted into "tab", and a rule cannot do that.
But a trigger does exactly that.

I am not sure whether this behavior of rules is a bug or a feature.
I am sure that it would be difficult to change...

			regards, tom lane

In response to

Responses

pgsql-general by date

Next:From: Marcin MazurekDate: 2000-08-30 21:16:12
Subject: Re: rules or trigers?
Previous:From: Tom LaneDate: 2000-08-30 21:06:41
Subject: Re: Post install - error

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group