Re: about partitioning

On 4/1/06, fufay <fufay(at)126(dot)com> wrote:
> 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;

Since the fields don't exist in news_001, it has to store them
somewhere - in the table it inherits from.

Inheritence is meant to be used to change something in the
substructure/child table/whatever.

If that object isn't in the child, it has to go back to the parent to
work out what to do (in your case, store the entry).

--
Postgresql & php tutorials
http://www.designmagick.com/