From: | "Foster, Stephen" <stephenlfoster(at)comcast(dot)net> |
---|---|
To: | <pgsql-general(at)postgresql(dot)org> |
Subject: | Setting expire date on insert/modify |
Date: | 2006-01-25 00:45:58 |
Message-ID: | 005f01c62148$b51cc000$2101a8c0@cfgod |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Glaesemann | 2006-01-25 00:57:38 | Re: Setting expire date on insert/modify |
Previous Message | Andrew - Supernews | 2006-01-25 00:11:00 | Re: NOT HAVING clause? |