Re: Setting expire date on insert/modify

["Foster, Stephen" <stephenlfoster@xxxxxxxxxxx>]

Michael, I tried that line in the trigger procedure with double quotes,
single quotes and without.  The only way it would save was with single
quotes and that is why you saw it that way.  I know it has to be some
sort of stupid syntax error but since I'm new to PostgreSQL (as far as
this level of coding) I have no idea what my error is.

If I try to save without which was what I did in the first place I
receive this using "ExpireDate := (date StartDate + integer NumOfDays);"

ERROR:  syntax error at or near "$1" at character 16
QUERY:  SELECT  (date  $1  + integer  $2 )
CONTEXT:  SQL statement in PL/PgSQL function "TF_BannerAd_Ads_InsMod"
near line 8

Full procedure again:

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;

The hard bits I'm getting easily but I keep getting hung up over simple
junk.

Thanks for the help,

Lee Foster/


-----Original Message-----
From: Michael Glaesemann [mailto:grzm@xxxxxxxxxxxxx] 
Sent: Tuesday, January 24, 2006 6:58 PM
To: Foster, Stephen
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: [GENERAL] Setting expire date on insert/modify


On Jan 25, 2006, at 9:45 , Foster, Stephen wrote:

> 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

Why are you single-quoting StartDate and NumOfDays? Single-quotes are  
generally used for string literals (e.g., text and varchar types). If  
you are trying to preserve the case of the variable name, you need to  
(always) use double-quotes, including when you define them.

Hope this helps a bit. I haven't looked through all of the code, so  
there may be other bugs lurking, but this is definitely one of the  
problems.

Michael Glaesemann
grzm myrealbox com



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

-- 
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