Setting expire date on insert/modify

Поиск
Список
Период
Сортировка
От Foster, Stephen
Тема Setting expire date on insert/modify
Дата
Msg-id 005f01c62148$b51cc000$2101a8c0@cfgod
обсуждение исходный текст
Ответ на Re: timestamp <-> ctime conversion question...  (Tom Lane <tgl@sss.pgh.pa.us>)
Ответы Re: Setting expire date on insert/modify  (Michael Glaesemann <grzm@myrealbox.com>)
Re: Setting expire date on insert/modify  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
Список pgsql-general
I'm having a problem converting a simple date routing to PLPGSQL.  I
know the error has to be something stupid but I'm missing it.  The error
is in the "ExpireDate := (date 'StartDate' + integer 'NumOfDays');" line
in the procedure below.  I didn't understand why I had to convert the
date to a string and back to a date but ok.  Below is the error I'm
getting a test and please tell me where I'm going wrong.  Also I include
the trigger procedure, trigger and insert.  Please remember that I'm a
MS-SQL guy migrating to PostgreSQL.

ERROR:  invalid input syntax for type date: "StartDate"
CONTEXT:  SQL statement "SELECT  (date 'StartDate' + integer
'NumOfDays')"
PL/pgSQL function "TF_BannerAd_Ads_InsMod" line 8 at assignment


CREATE OR REPLACE FUNCTION "TF_BannerAd_Ads_InsMod"()
  RETURNS "trigger" AS
$BODY$
DECLARE
    ExpireDate timestamptz;    -- Date the Banner Ad will expire.
    StartDate char(10);    -- Date the Banner Ad was created or
renewed
    NumOfDays char(10);    -- Number of Dates the Ad will be in
place.
BEGIN
    StartDate := to_char(NEW.bannerad_creationdate, 'YYYY-MM-DD');
    NumOfDays := to_char(NEW.bannerad_term, '99999');
    ExpireDate := (date 'StartDate' + integer 'NumOfDays');
    IF (TG_OP = 'UPDATE') THEN
        UPDATE public.bannerad_ads SET bannerad_expiredate =
ExpireDate WHERE bannerad_id = OLD.bannerad_id;
    ELSIF (TG_OP = 'INSERT') THEN
        UPDATE public.bannerad_ads SET bannerad_expiredate =
ExpireDate WHERE bannerad_id = NEW.bannerad_id;
    END IF;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER "Trg_BannerAd_Ads_InsMod" BEFORE INSERT OR UPDATE
   ON bannerad_ads FOR EACH ROW
   EXECUTE PROCEDURE public."TF_BannerAd_Ads_InsMod"();

insert into bannerad_ads
(bannerad_href,bannerad_alttext,bannerad_filename,bannerad_creationdate,
bannerad_term) values ('http://www.equilt.com','ElectricQuilt Southern
Music','EQMBannerAd4.gif','2006-01-20 01:00:00-05',18250);


--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.22/238 - Release Date:
1/23/2006



В списке pgsql-general по дате отправления:

Предыдущее
От: Andrew - Supernews
Дата:
Сообщение: Re: NOT HAVING clause?
Следующее
От: Michael Glaesemann
Дата:
Сообщение: Re: Setting expire date on insert/modify