Thread: intervals
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
On Thu, Nov 3, 2011 at 10:29 AM, Edward W. Rouse <erouse@comsquared.com> wrote:
> expire := '% days'::interval, limit;
A couple ways spring to mind immediately. Using 10 as the example:
expire := 10 * '1 day'::INTERVAL;
expire := ('10' || ' days')::INTERVAL;
Hi Edward, "LIMIT" is a keyword, which is where you are getting your errors. Did you try a different variable name? Best, Jonathan On Nov 3, 2011, at 1:29 PM, Edward W. Rouse wrote: > 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 > > > > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
That was part of the problem. Thanks for the info. > -----Original Message----- > From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql- > owner@postgresql.org] On Behalf Of Jonathan S. Katz > Sent: Thursday, November 03, 2011 1:41 PM > To: Edward W. Rouse > Cc: pgsql-sql@postgresql.org > Subject: Re: [SQL] intervals > > Hi Edward, > > "LIMIT" is a keyword, which is where you are getting your errors. Did > you try a different variable name? > > Best, > > Jonathan > > On Nov 3, 2011, at 1:29 PM, Edward W. Rouse wrote: > > > 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 > > > > > > > > > > -- > > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-sql > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql
In conjunction with Jonathan, this has gotten me to the point where it works, sort of. Now I just need to change it so
thatit does more than 1 at a time. Since it currently isn't in a loop, it affects one and quits. But I needed to get it
todo that first, putting it in a loop should be the easy part.
Thanks.
> -----Original Message-----
> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-
> owner@postgresql.org] On Behalf Of bricklen
> Sent: Thursday, November 03, 2011 1:44 PM
> To: Edward W. Rouse
> Cc: pgsql-sql@postgresql.org
> Subject: Re: [SQL] intervals
>
> On Thu, Nov 3, 2011 at 10:29 AM, Edward W. Rouse
> <erouse@comsquared.com> wrote:
> > expire := '% days'::interval, limit;
>
> A couple ways spring to mind immediately. Using 10 as the example:
>
> expire := 10 * '1 day'::INTERVAL;
> expire := ('10' || ' days')::INTERVAL;
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
On Nov 3, 2011, at 13:43, bricklen wrote:
> On Thu, Nov 3, 2011 at 10:29 AM, Edward W. Rouse <erouse@comsquared.com> wrote:
>> expire := '% days'::interval, limit;
>
> A couple ways spring to mind immediately. Using 10 as the example:
>
> expire := 10 * '1 day'::INTERVAL;
good.
> expire := ('10' || ' days')::INTERVAL;
bad.
Michael Glaesemann
grzm seespotcode net