Hello!
I have problem with certain RULE. As stated in documentation, in case
"ON INSERT" rule, the rule is executed AFTER insertion to the "TO" table.
And this is the true, if all values that are about to be inserted are
places in VALUES brackets.
Unfortunately, when I want to use SERIAL domain for primary key,
this does not work --- instead of new value read from sequence,
the rule sees NULL.
Here's the schema:
CREATE TABLE "users"
(
"id_user" SERIAL NOT NULL,
"first" VARCHAR(24) NOT NULL DEFAULT '(imi\352)',
"last" VARCHAR(32) NOT NULL DEFAULT '(nazwisko)',
"email" VARCHAR(24) DEFAULT NULL,
"vip" BOOLEAN NOT NULL DEFAULT 'f',
"ed" BOOLEAN NOT NULL DEFAULT 'f',
PRIMARY KEY ("id_user")
);
CREATE TABLE "permissions"
(
"id_permission" INTEGER NOT NULL,
"name" VARCHAR(32) NOT NULL UNIQUE,
PRIMARY KEY ("id_permission")
);
CREATE TABLE "given_permissions"
(
"id_permission" INTEGER NOT NULL,
"id_user" INTEGER NOT NULL,
PRIMARY KEY ("id_permission", "id_user"),
FOREIGN KEY ("id_permission")
REFERENCES "permissions"
ON DELETE CASCADE,
FOREIGN KEY ("id_user")
REFERENCES "users"
ON DELETE CASCADE
);
CREATE RULE "on_insert_to_users" AS
ON INSERT TO "users"
DO
(
INSERT INTO "given_permissions" VALUES (1, NEW."id_user");
INSERT INTO "given_permissions" VALUES (2, NEW."id_user");
INSERT INTO "given_permissions" VALUES (3, NEW."id_user");
);
-- initial inserts...
INSERT INTO "permissions" VALUES ( 1, 'Wysy\263anie komentarzy');
INSERT INTO "permissions" VALUES ( 2, 'Zg\263aszanie aktualno\266ci');
INSERT INTO "permissions" VALUES ( 3, 'Zg\263aszanie ankiet');
...
So, if I issue statement like that:
INSERT
INTO "users" ("id_user", "first", "last")
VALUES (0, 'Administrator', 'systemowy');
then the rule works --- but please note, that I do not use the
SERIAL feature in this case.
But if I do something like this:
INSERT
INTO "users" ("first", "last")
VALUES ("First", "Last");
or
INSERT
INTO "users"
DEFAULT VALUES;
then this is what I receive:
ERROR: ExecAppend: Fail to add null value in not null attribute id_user
When I drop the rule, those two above statements work, but I have
to place default permissions manually.
Is this a bug? Is there a workaround for this (expect inserting
data manually...)?
I have PSQL 7.2.1 installed under Windows XP (CygWin). The same
happens either when I try to place new user using JDBC or when
I do that by hand from psql client console.
