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