SERIAL and RULE of "ON INSERT" kind

From: "Tomisaw Kityski" <cromax(at)amiga(dot)pl>
To: pgsql-general(at)postgresql(dot)org
Subject: SERIAL and RULE of "ON INSERT" kind
Date: 2002-06-24 08:08:23
Message-ID: af6k17$5ih$1@news.tpi.pl
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Mourad EL HADJ MIMOUNE 2002-06-24 08:16:07 Table name length (maximum identifier length)
Previous Message Giuseppe Tanzilli - CSF 2002-06-24 07:54:37 Re: SQL server application porting headache