Re: trigger / to_char()-function / extract()-function
От | Michael Fuhr |
---|---|
Тема | Re: trigger / to_char()-function / extract()-function |
Дата | |
Msg-id | 20060113055425.GA88488@winnie.fuhr.org обсуждение исходный текст |
Ответ на | trigger / to_char()-function / extract()-function (mushroom2@uboot.com) |
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: