Setting expire date on insert/modify

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

In response to

Responses

Browse pgsql-general by date

  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?