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

On Thu, Jan 12, 2006 at 01:37:46PM +0000, mushroom2(at)uboot(dot)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