pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedintervals
6+ messages / 4 participants
[nested] [flat]
* intervals
@ 2011-11-03 17:29 Edward W. Rouse <erouse@comsquared.com>
0 siblings, 2 replies; 6+ messages in thread
From: Edward W. Rouse @ 2011-11-03 17:29 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: intervals
@ 2011-11-03 17:41 Jonathan S. Katz <jonathan.katz@excoventures.com>
parent: Edward W. Rouse <erouse@comsquared.com>
1 sibling, 1 reply; 6+ messages in thread
From: Jonathan S. Katz @ 2011-11-03 17:41 UTC (permalink / raw)
To: Edward W. Rouse <erouse@comsquared.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: intervals
@ 2011-11-03 17:43 bricklen <bricklen@gmail.com>
parent: Edward W. Rouse <erouse@comsquared.com>
1 sibling, 2 replies; 6+ messages in thread
From: bricklen @ 2011-11-03 17:43 UTC (permalink / raw)
To: Edward W. Rouse <erouse@comsquared.com>; +Cc: pgsql-sql
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;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: intervals
@ 2011-11-03 17:56 Edward W. Rouse <erouse@comsquared.com>
parent: Jonathan S. Katz <jonathan.katz@excoventures.com>
0 siblings, 0 replies; 6+ messages in thread
From: Edward W. Rouse @ 2011-11-03 17:56 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: intervals
@ 2011-11-03 17:59 Edward W. Rouse <erouse@comsquared.com>
parent: bricklen <bricklen@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Edward W. Rouse @ 2011-11-03 17:59 UTC (permalink / raw)
To: 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 that it 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 to do 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: intervals
@ 2011-11-03 18:20 Michael Glaesemann <grzm@seespotcode.net>
parent: bricklen <bricklen@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Michael Glaesemann @ 2011-11-03 18:20 UTC (permalink / raw)
To: bricklen <bricklen@gmail.com>; +Cc: Edward W. Rouse <erouse@comsquared.com>; 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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2011-11-03 18:20 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2011-11-03 17:29 intervals Edward W. Rouse <erouse@comsquared.com>
2011-11-03 17:41 ` Jonathan S. Katz <jonathan.katz@excoventures.com>
2011-11-03 17:56 ` Edward W. Rouse <erouse@comsquared.com>
2011-11-03 17:43 ` bricklen <bricklen@gmail.com>
2011-11-03 17:59 ` Edward W. Rouse <erouse@comsquared.com>
2011-11-03 18:20 ` Michael Glaesemann <grzm@seespotcode.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox