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
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
