From: Dmitri Bichko <dbichko@genpathpharma.com>
To: pgsql-sql@postgresql.org
Subject: Question about insert/update RULEs.
Date: Sun, 9 Jan 2005 06:45:54 -0500
Message-ID: <7A4ADADFC8AFF0478D47F63BEDD57CE30D0A00@gpmail.gphq.genpathpharma.com> (raw)
Hello,
I am trying to use the RULE system to simulate updatable views, with the
final goal of simulating polymorphism in an ORM-like system
(Class::DBI).
Same old idea - when selecting from foo C:DBI checks the "type" column
and reblesses (I guess casts, in non-perl world) the object to the
appropriate class (which will lazily fetch the additional columns).
Here's what I have so far:
CREATE TABLE "foo" (
"foo_id" serial PRIMARY KEY,
"type" varchar NOT NULL DEFAULT 'base' CONSTRAINT types CHECK
(type IN ('base', 'bar'))
"a" varchar NOT NULL,
"b" varchar DEFAULT 'some text',
);
CREATE TABLE "foo_bar" (
"foo_id" integer PRIMARY KEY CONSTRAINT foo REFERENCES foo (foo_id) ON
DELETE CASCADE ON UPDATE CASCADE DEFERABLE,
"c" varchar
);
CREATE VIEW "bar" AS
SELECT f.foo_id, f.a, f.b, b.c
FROM foo f JOIN foo_bar b USING(foo_id);
CREATE RULE "bar_insert" AS ON INSERT TO "bar"
DO INSTEAD (
INSERT INTO "foo" (foo_id, type, a, b) VALUES
(NEW.foo_id, 'bar', NEW.a, NEW.b);
INSERT INTO "foo_bar" (foo_id, c) VALUES (NEW.foo_id,
NEW.c);
);
CREATE RULE "bar_update" AS ON UPDATE TO "bar"
DO INSTEAD (
UPDATE "foo" SET a = NEW.a, b = NEW.b WHERE foo_id =
OLD.foo_id;
UPDATE "foo_bar" SET c = NEW.c WHERE foo_id =
OLD.foo_id;
);
CREATE RULE "bar_delete" AS ON DELETE TO "bar"
DO INSTEAD
DELETE FROM "foo" WHERE foo_id = OLD.foo_id;
The problem is that for the sequence to do the right thing, I have to
select nextval first in a separate query and then pass it explicitely to
INSERT INTO "bar" (which C:DBI does anyway, but I'd like to do better).
If I were to do this:
foo insert: foo_id = COALESCE(NEW.foo_id, nextval('foo_foo_id_seq')),
foo_bar insert: foo_id = COALESCE(NEW.foo_id,
currval('foo_foo_id_seq')),
Will the currval() be guaranteed to be the same value that the nextval()
got? I am not quite sure what the "scope" of currval() is.
Also, using COALESCE is the only way I can think of to deal with default
values, is there a way to have the rule respect the defaults of the
underlying tables without having to specify them again in the rule
itself (ie COALESCE(NEW.b, 'some text')?
Secondly, the fact that UPDATE queries work seems a little magical to me
(I got the rule examples from the docs): if a user does not specify a
value for a column in an update, is its NEW value set to OLD? Also, it
seems that I can specify arbitrary WHERE clauses for the updates and
they work properly, but it seems that the rule only matches on the
primary key - why does this work?
I've been having trouble finding documentation about what other
information (in addition to NEW and OLD) about the query being rewritten
is available to the INSTEAD queries - mainly because I don't know what
these things are called, so I don't know where to look (ie it's not just
a query, it's not a function - what is this block referred to as?).
Sorry, that was a little stream of consiousness. More generally - is
there anything else I should be aware of when trying this approach?
Thanks for the help, if this works it will be quite an elegant solution
to an annoying problem.
Dmitri
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: dbichko@genpathpharma.com
Subject: Re: Question about insert/update RULEs.
In-Reply-To: <7A4ADADFC8AFF0478D47F63BEDD57CE30D0A00@gpmail.gphq.genpathpharma.com>
* 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