trigger / to_char()-function / extract()-function - Mailing list pgsql-novice

I have a table as followed

CREATE TABLE "DIM_TIME_DAY"
(
  "TD_DWH_CREATE" date NOT NULL,
  "TD_DATE" varchar(60),
  "TD_NAME" varchar(10),
  "TD_DAYNUM" int8,
  "TD_WEEKDAYNUM" int8,
  "TD_YEAR" int8,
  "TD_ISWEEKEND" char(1),
  CONSTRAINT "DIM_TIME_DAY_KEY" PRIMARY KEY ("TD_DWH_CREATE")
)
WITHOUT OIDS;
ALTER TABLE "DIM_TIME_DAY" OWNER TO postgres;
GRANT ALL ON TABLE "DIM_TIME_DAY" TO postgres WITH GRANT OPTION;
GRANT SELECT, UPDATE, INSERT ON TABLE "DIM_TIME_DAY" TO GROUP "DWH_ACCESS";

When an new "TD_DWH_CREATE" key is created the other fields automatically have to fill, i.e. "TD_DATE" as textform of
thedate Day, DD.MM.YYYY, the "TD_NAME" as weekdayname and so on. 

I have created a triggerfunction called "Set_DateTime"() like this
========================================================
REATE OR REPLACE FUNCTION "Set_TimeDay"() RETURNS "trigger" AS
BEGIN
    -- Pr�fe ob TD_DWH_CREATE nicht NULL ist
    IF NEW."TD_DWH_CREATE" IS NULL THEN
    RAISE EXCEPTION '"Feld TD_DWH_CREATE darf nicht NULL sein"';
    END IF;

    -- Datum entsprechend ausgeben
IF NEW."TD_DWH_CREATE" is not null then
--Only tested--
--Insert into "DIM_TIME_DAY"("TD_DATE") Values (to_char(new."TD_DWH_CREATE", 'Day, DD Mon YYYY'));
--Only tested--
--Update "DIM_TIME_DAY" set "TD_DATE"=to_char("TD_DWH_CREATE", 'Day, DD Mon YYYY');

--Date in format "Saturday, 14.01.2006"2
--new."TD_DATE" := to_char(new."TD_DWH_CREATE", 'Day, DD.MM.YYY')

--Name of the Day i.e. Saturday
--new."TD_NAME" := to_char(new."TD_DWH_CREATE", 'Day');

--Day of year i.e. 250
--NEW."TD_DAYNUM" :=extract(DOW FROM TIMESTAMP NEW."TD_DWH_CREATE");

--Day of week 0-6 0=sunday
--NEW."TD_WEEKDAYNUM" :=extract(DOY FROM TIMESTAMP NEW."TD_DWH_CREATE");

-year as number i.e. 2006
--NEW."TD_YEAR" :=extract(YEAR FROM TIMESTAMP NEW."TD_DWH_CREATE");

--If saturday or sunday 'Y'otherwise 'N'
  --IF NEW."TD_DAYNUM"=0 or If NEW."TD_DAYNUM"=1 then
  --NEW."TD_ISWEEKDAY"='Y';
  --Else NEW."TD_ISWEEKDAY"='N';
  --END IF;
END IF;
RETURN NEW;
END;
LANGUAGE 'plpgsql' VOLATILE;
========================================================
I'm trying to fill the other fields after insert or update the field "TD_DWH_CREATE".

1. If I try to use only the insert term (above) after >"TD_DWH_CREATE" is set I get an error "null value >column
"TD_DWH_CREATE"
violates not-null constraint" but there are no other entries in this table.

2. If I try to use only the update term (above) after "TD_DWH_CREATE" is set I get an error:
>"Error:    syntax error at or near "$1" at sign 28
>Query:    Update "DIM_TIME_DAY" set $1=to_char($2, 'Day, >DD Mon YYYY')
>Kontext: Pl/pgSQL function "Set_TimeDay" line 11 at >SQL statement"

3. If I try the "new."TD_NAME" := to_char(new."TD_DWH_CREATE", 'Day');"-part of the above mentioned function I get no
error,but my field is still empty. 

4. And I think another problem will be the extract() partss like "NEW."TD_DAYNUM" :=extract(DOW FROM TIMESTAMP
NEW."TD_DWH_CREATE");"because here a timestamp must be refered but my variable is of type date. 

Can I cast date into timestamp, i.e with a predefined function in postgreSQL or can somebody give me a tip how a
functionfor casting date into timestamp can look like? 

Can anybody help to fill my fields as I aim at.

And I like to know if there is any possibility to use a trigger for filling another table based on this created table?
Forexample I have another table like 
=====================================================
CREATE TABLE "FACT_TURNOVER_CPD"
(
---refers to table "DIM_CUSTOMER"--
  "C_DWH_CREATE" date NOT NULL,
  "C_ID" int8 NOT NULL,
---refers to table "DIM_PRODUCT"--
  "P_DWH_CREATE" date NOT NULL,
  "P_ID" int8 NOT NULL,
---refers to table "DIM_TIME_DAY"--
  "TD_DWH_CREATE" date NOT NULL,

--value which will be set by ETL-loading process
  "DAILYTURNOVER" float8 NOT NULL,

--Constraints--
  CONSTRAINT "FACT_TURNOVER_CPD_KEY" PRIMARY KEY ("C_DWH_CREATE", "C_ID", "P_DWH_CREATE", "P_ID", "TD_DWH_CREATE"),
  CONSTRAINT "FTCPD_FK_1" FOREIGN KEY ("C_DWH_CREATE", "C_ID") REFERENCES "DIM_CUSTOMER" ("C_DWH_CREATE", "C_ID") ON
UPDATERESTRICT ON DELETE RESTRICT, 
  CONSTRAINT "FTCPD_FK_2" FOREIGN KEY ("P_DWH_CREATE", "P_ID") REFERENCES "DIM_PRODUCT" ("P_DWH_CREATE", "P_ID") ON
UPDATERESTRICT ON DELETE RESTRICT, 
  CONSTRAINT "FTCPD_FK_3" FOREIGN KEY ("TD_DWH_CREATE") REFERENCES "DIM_TIME_DAY" ("TD_DWH_CREATE") ON UPDATE RESTRICT
ONDELETE RESTRICT 
)
WITHOUT OIDS;
ALTER TABLE "FACT_TURNOVER_CPD" OWNER TO postgres;
GRANT SELECT, UPDATE, INSERT ON TABLE "FACT_TURNOVER_CPD" TO GROUP "DWH_ACCESS";
GRANT ALL ON TABLE "FACT_TURNOVER_CPD" TO postgres WITH GRANT OPTION;
=====================================================
and all values excepting "DAILYTURNOVER" are keys to other tables. How can I fill this table with the available values
fromthe other tables excepting "DAILYTURNOVER"? 

I want to thank everybody who read this (long) mail untill here!!!

Please help me.
Regards
-----
schwarzfunk - 10 cent vom handy
der g�nstigste prepaid tarif in deutschland!