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