Re: Re: Add generate_series(date,date) and generate_series(date,date,integer)

David Steele <david(at)pgmasters(dot)net> writes:
> On 3/17/16 11:55 AM, David G. Johnston wrote:
>> With the first argument casted to date it doesn't matter whether you
>> cast the second argument as the pseudo-type anyelement will take its
>> value from the first argument and force the second to date.

> Ah, I see.

FWIW, there isn't any "anyelement" involved here, AFAICT. The set
of functions we have today is:

regression=# \df generate*
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+---------------------+-----------------------------------+--------------------------------------------------------------------+--------
pg_catalog | generate_series | SETOF bigint | bigint, bigint | normal
pg_catalog | generate_series | SETOF bigint | bigint, bigint, bigint | normal
pg_catalog | generate_series | SETOF integer | integer, integer | normal
pg_catalog | generate_series | SETOF integer | integer, integer, integer | normal
pg_catalog | generate_series | SETOF numeric | numeric, numeric | normal
pg_catalog | generate_series | SETOF numeric | numeric, numeric, numeric | normal
pg_catalog | generate_series | SETOF timestamp with time zone | timestamp with time zone, timestamp with time zone, interval | normal
pg_catalog | generate_series | SETOF timestamp without time zone | timestamp without time zone, timestamp without time zone, interval | normal
pg_catalog | generate_subscripts | SETOF integer | anyarray, integer | normal
pg_catalog | generate_subscripts | SETOF integer | anyarray, integer, boolean | normal
(10 rows)

Now, generate_subscripts is a totally different function that *should*
have a separate name; it does not take a couple of endpoints. That's
not much of an argument for inventing different names for some of
the functions that do work with a pair of endpoints.

The real problem is that if we invent generate_series(date,date,integer)
then, given the problem "generate_series(date,unknown,unknown)"
the parser's ambiguous-function rules[1] will resolve that as
generate_series(date,date,integer), on the grounds that that provides
more exact type matches (i.e. 1) than any of the other alternatives.
Previously, the same call would have been resolved as
generate_series(timestamptz,timestamptz,interval) on the grounds that
that's reachable by casting and timestamptz is a preferred type.
So if you had written such a call with an interval literal and didn't
bother to cast the literal, your code would break.

We could avoid that problem if the new function were defined as
generate_series(date,date,interval), but then I'm not certain
what the code ought to do if the given interval is not a multiple
of a day.

One idea that might be worth considering is to define the function
as generate_series(date,date,interval) returns timestamp (without
time zone). The point here would be only to move the behavior for
date inputs as far as getting timestamp without tz rather than
timestamp with tz; which would at least save some timezone rotations
in typical use, as well as rather debatable semantics. (The fact
that timestamptz is the preferred type in this hierarchy isn't
really doing us any favors here.)

regards, tom lane

[1] http://www.postgresql.org/docs/devel/static/typeconv-func.html