From: Ben Morrow <ben@morrow.me.uk>
To: pgsql-sql@postgresql.org
Subject: Rules and sequences
Date: Wed, 26 May 2010 19:16:20 +0100
Message-ID: <20100526181620.GA54996@osiris.mauzo.dyndns.org> (raw)
I am trying to implement a fairly standard 'audit table' setup, but
using rules instead of triggers (since it should be more efficient).
However, I'm running into problems when one of the audited tables has a
'serial' column that is allowed to default:
create table foo (id serial, bar text);
create table audit (ix bigserial, rec text);
create rule audit_insert as on insert to foo do also
insert into audit (rec) values ((new.*)::text);
insert into foo (bar) values ('baz');
select * from foo;
id | bar
----+-----
1 | baz
(1 row)
select * from audit;
ix | rec
----+---------
1 | (2,baz)
(1 row)
I can see why this is happening (the rule is essentially a macro, so the
NEW expression gets expanded twice, including the nextval call, so the
sequence is incremented twice), but is there any way to prevent it? Some
way of 'materialising' the NEW row so it is just plain values rather
than a list of expressions?
Ben
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: ben@morrow.me.uk
Subject: Re: Rules and sequences
In-Reply-To: <20100526181620.GA54996@osiris.mauzo.dyndns.org>
* 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