Re: Setting expire date on insert/modify - Mailing list pgsql-general

From Foster, Stephen
Subject Re: Setting expire date on insert/modify
Date
Msg-id 007701c62157$465eb100$2101a8c0@cfgod
Whole thread Raw
In response to Re: Setting expire date on insert/modify  (Stephan Szabo <sszabo@megazone.bigpanda.com>)
List pgsql-general
Thanks guys it finally worked correctly.  Just in case someone else get
hung on this type of thing here is the working trigger function.

CREATE OR REPLACE FUNCTION "TF_BannerAd_Ads_InsMod"()
  RETURNS "trigger" AS
$BODY$
DECLARE
    ExpireDate timestamptz;    -- Date the Banner Ad will expire.
BEGIN
    NEW.bannerad_expiredate := (NEW.bannerad_creationdate::date +
NEW.bannerad_term::int4);
    RETURN NEW;
END;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE;

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Stephan Szabo
Sent: Tuesday, January 24, 2006 7:18 PM
To: Foster, Stephen
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Setting expire date on insert/modify

On Tue, 24 Jan 2006, Foster, Stephen wrote:

> 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');

I think something like:

ExpireDate := CAST(NEW.bannerad_creationdate as Date) +
NEW.bannerad_term;

may work for you.


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly

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



pgsql-general by date:

Previous
From: Doug McNaught
Date:
Subject: Re: Postgresql Segfault in 8.1
Next
From: Tom Lane
Date:
Subject: Re: pgxml