Re: Re: Add generate_series(date,date) and generate_series(date,date,integer) - Mailing list pgsql-hackers

On 3/17/16 11:30 AM, David G. Johnston wrote:
> On Thu, Mar 17, 2016 at 7:57 AM, Corey Huinker <corey.huinker@gmail.com
> <mailto:corey.huinker@gmail.com>>wrote:
> 
>     On Thu, Mar 17, 2016 at 10:00 AM, David Steele <david@pgmasters.net
>     <mailto:david@pgmasters.net>> wrote:
> 
>         On 3/17/16 4:49 AM, Dean Rasheed wrote:
> 
>         > On 16 March 2016 at 23:32, David Steele <david@pgmasters.net <mailto:david@pgmasters.net>> wrote:
>         >
>         >>
>         >> I think in this case it comes down to a committer's judgement so I have
>         >> marked this "ready for committer" and passed the buck on to Álvaro.
>         >
>         > So I was pretty much "meh" on this patch too, because I'm not
>         > convinced it actually saves much typing, if any.
>         >
>         > However, I now realise that it introduces a backwards-compatibility
>         > breakage. Today it is possible to type
>         >
>         > SELECT * FROM generate_series('01-01-2000'::date, '01-04-2000', '7 days');
> 
>         It can also be broken as below and this is even scarier to me:
> 
> 
> Above and below are the same query​...

Not sure I agree.  My point was that even if developers were pretty
careful with their casting (or are using two actual dates) then there's
still possibility for breakage.

>         postgres=# SELECT * FROM generate_series('01-01-2000'::date,
>         '01-04-2000'::date, '7 days');
>         ERROR:  invalid input syntax for integer: "7 days"
>         LINE 1: ...te_series('01-01-2000'::date, '01-04-2000'::date, '7
>         days');
> <...>
> 
>     I see two ways around this: 
> 
>     1. Drop the step parameter entirely. My own use cases only ever
>     require the step values 1 and -1, and which one the user wants can
>     be inferred from (start < end). This would still change the output
>     type where a person wanted timestamps, but instead input two dates.
> 
> ​Undesirable.​

Very undesirable.  Week intervals are a very valid use case and I don't
like the automagic interval idea.

> 
>     2. Rename the function date_series() or generate_series_date()
> 
>     I still think this is an important function because at the last
>     several places I've worked, I've found myself manufacturing a query
>     where some event data is likely to have date gaps, but we want to
>     see the date gaps or at least have the 0 values contribute to a
>     later average aggregate.
> 
> 
> ​I'd call it "generate_dates(...)" and be done with it.
> 
> We would then have:
> 
> generate_series()
> generate_subscripts()
> generate_dates()

To me this completely negates the idea of this "just working" which is
why it got a +1 from me in the first place.  If I have to remember to
use a different function name then I'd prefer to just cast on the
timestamp version of generate_series().

Sorry, but this is now -1 from me, at least for this commitfest.  While
I like the idea I think it is far too late to be redesigning such a
minor feature.

-- 
-David
david@pgmasters.net