From: "Edward W. Rouse" <erouse@comsquared.com>
To: <pgsql-sql@postgresql.org>
Subject: intervals
Date: Thu, 3 Nov 2011 13:29:40 -0400
Message-ID: <00ee01cc9a4e$2b41f050$81c5d0f0$@com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: 7bit
Thread-Index: AcyaTir/3QcZJtJJS/CV72EP/KcQ6g==
Content-Language: en-us

I am writing a procedure and am having a little difficulty with an interval.
In the DECLARE section I have:

expire interval := '30 days'::interval;

which seems to work fine. In the BEGIN block I need to pull out the number
of days from a table and update the expire interval to that. But I can't
quite seem to get the replacement correct.

First I use "SELECT value into limit from" to get the new limit value. The
value is text, but I have also tried with SELECT (value::integer) into limit
from" with the same result. The error occurs here:

expire := '%  days'::interval, limit;

Now I have tried several different ways to get a new interval, such as '' ||
limit || days''::interval; and other various differently quoted variations,
but haven't found the one that works yet. I am still trying various
combinations, but thought that someone on the list has probably done this
already. I may, in fact, be doing it entirely wrong. So if someone knows the
correct/better/easier way to create a dynamic interval I'd appreciate a
clue.

Thanks.


Edward W. Rouse