From: | "fufay" <fufay(at)126(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | about partitioning |
Date: | 2006-03-31 17:53:07 |
Message-ID: | e0jq9v$1noq$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
dear all,
i created a master table and a sub table which inherits the main table.
and then i made a trigger and a function that want to keep the master table
empty.
but the trigger didn't work anyway.when i inserted data into the table
"news",both the master table
and the sub table were inserted.
why? i just want the empty master table,any good ideas?
lots of thanks for all.
here r DDls:
-------------------------------------------------------------------------
--master table
CREATE TABLE "public"."news" (
"id" SERIAL,
"title" VARCHAR(100) NOT NULL,
"content" VARCHAR NOT NULL,
"author" VARCHAR(50) NOT NULL,
"date" DATE DEFAULT now(),
CONSTRAINT "news_pkey" PRIMARY KEY("id")
)WITHOUT OIDS;
--rule
CREATE RULE "news_current_partition" AS ON INSERT TO "public"."news"
DO INSTEAD (INSERT INTO news_001 (title, content, author) VALUES (new.title,
new.content, new.author));
--trigger
CREATE TRIGGER "news_triggers" BEFORE INSERT
ON "public"."news" FOR EACH ROW
EXECUTE PROCEDURE "public"."deny_insert"();
--function
CREATE OR REPLACE FUNCTION "public"."deny_insert" () RETURNS trigger AS
$body$
BEGIN
RETURN NULL;
END;
$body$
LANGUAGE 'plpgsql' IMMUTABLE CALLED ON NULL INPUT SECURITY INVOKER;
--sub table
CREATE TABLE "public"."news_001" (
CONSTRAINT "news_001_date_check" CHECK ((date >= '2006-03-29'::date) AND
(date < '2006-04-28'::date))
) INHERITS ("public"."news")
WITHOUT OIDS;
CREATE INDEX "news_001_index" ON "public"."news_001"
USING btree ("id");
------------------------------------------------------------------------------------------
From | Date | Subject | |
---|---|---|---|
Next Message | Ed L. | 2006-03-31 18:51:20 | Re: pg 8.1.2 performance issue |
Previous Message | postgresql | 2006-03-31 17:36:07 | giving users access to specific databases |