From: "Josh Berkus" <josh@agliodbs.com>
Subject: Re: Messy Casts, Is there a better way?
To: Larry Rosenman <ler@lerctr.org>, pgsql-sql@postgresql.org
Date: Tue, 15 Oct 2002 09:28:12 -0700
Message-ID: <web-1787228@davinci.ethosmedia.com>
In-Reply-To: <1034676979.436.1.camel@lerlaptop.lerctr.org>
MIME-Version: 1.0
Content-Type: text/plain; charset="ISO-8859-1"
Content-Transfer-Encoding: 8bit

Larry,

> contract_start date
> contract_term  int (term in MONTHS)
> 
> I want to calculate the contract end date.  I came up with:
> CREATE FUNCTION "get_contract_end" (integer) RETURNS date AS 'SELECT
> cast(contract_start + cast(cast(contract_term as text) || '' month''
> as
> interval) as date)
> FROM circuit
>     WHERE internal_id = $1;' LANGUAGE 'sql';

Not with those data types.   Plus, there is an implicit conversion date
--> timestamp --> date in the above, which can get you in trouble.

You could use TIMESTAMP and INTERVAL instead:

contract_start TIMESTAMP WITHOUT TIME ZONE
contract_term INTERVAL

select (contract_start + contract_term) as contract_end;

Simple, neh?  The only trick is on the end of saving the data.   You
have the user input an integer, then save (using RULES or your
interface code):
"interval"(cast($term as varchar) || ' months')

This approach makes you do a little more work on the data entry end of
things, but speeds up querying considerably.   Also, should your
company policy change in the future to permit contract terms in weeks
or years, you will be ready to accomodate it.

-Josh Berkus