Thread: trigger / to_char()-function / extract()-function

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!

On Thu, Jan 12, 2006 at 01:37:46PM +0000, mushroom2@uboot.com wrote:
> I have created a triggerfunction called "Set_DateTime"() like this
> ========================================================
> REATE OR REPLACE FUNCTION "Set_TimeDay"() RETURNS "trigger" AS

Obviously REATE should be CREATE, and this code won't load without
the function body being quoted.

> 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;

Since TD_DWH_CREATE has a NOT NULL constraint you can omit this
check unless you want that particular error message or if it's
important that the rest of the function not execute if that column
is NULL.  The NOT NULL constraint will be checked after the trigger.

>     -- Datum entsprechend ausgeben
> IF NEW."TD_DWH_CREATE" is not null then

If you keep the earlier check for NULL then this check has no purpose
since we can't get here if TD_DWH_CREATE is NULL.

> --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');

Get rid of these -- you want to modify the record being inserted
or updated, not insert a new record or update the entire table.  As
you may have already discovered, you could go into infinite recursion
if the trigger function does something that invokes itself again,
and again, and again....

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

Uncomment this, add a semicolon to terminate the statement, and
change YYY to YYYY if you want a 4-digit year as the comment
indicates.  If you don't want trailing blanks after the day name
then change Day to FMDay.

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

Uncomment this.

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

Uncomment this and change DOW to DOY if you want the day of the year
as the comment indicates.  Get rid of the word TIMESTAMP.

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

Uncomment this and change DOY to DOW if you want the day of the
week as the comment indicates and the column name implies.  Get rid
of the word TIMESTAMP.

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

Uncomment this and get rid of the word TIMESTAMP.

> --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;

Change TD_DAYNUM to TD_WEEKDAYNUM if you want this code to agree
with the changes suggested above, which are based on the comments
you wrote.  Check for 0 (Sunday) and 6 (Saturday), not 0 and 1
(Monday).  Change TD_ISWEEKDAY to TD_ISWEEKEND because that's what
the table definition has.  This section of code could be simplified
as

  NEW."TD_ISWEEKEND" := NEW."TD_WEEKDAYNUM" IN (0, 6);

> END IF;

Get rid of this if you removed the corresponding IF.

> RETURN NEW;
> END;
> LANGUAGE 'plpgsql' VOLATILE;

Close the quotes for the function body, add a CREATE TRIGGER
statement, and you're done.  Hopefully I caught everything :-)

> And I like to know if there is any possibility to use a trigger
> for filling another table based on this created table?

A trigger on one table can populate another table with the appropriate
INSERT commands, but your next example didn't provide enough
information for us to give much advice.

--
Michael Fuhr