On Tue, Jun 27, 2017 at 10:12 AM, Colin 't Hart <colinthart@gmail.com> wrote:
> On 27 Jun 2017, at 17:06, Merlin Moncure <mmoncure@gmail.com> wrote:
>>
>>> On Tue, Jun 27, 2017 at 10:01 AM, Colin 't Hart <colinthart@gmail.com> wrote:
>>> Hi,
>>>
>>> The following rather contrived example illustrates that lag(), lead()
>>> (and probably other functions) can't automatically cast an integer to
>>> a bigint:
>>>
>>> select lag(sum,1,0) over () from (select sum(generate_series) over
>>> (order by generate_series) from generate_series(1,10)) x;
>>> ERROR: function lag(bigint, integer, integer) does not exist
>>> LINE 1: select lag(sum,1,0) over () from (select sum(generate_series...
>>> ^
>>> HINT: No function matches the given name and argument types. You
>>> might need to add explicit type casts.
>>>
>>>
>>> I guess this is because the lag() and lead() functions take any type,
>>> and hence the default must be of the same type.
>>> This had me stumped for a few while until I realised that the types
>>> were different.
>>>
>>> Would there be any way to implement an automatic conversion?
>>>
>>> On the off-chance that this is actually a bug, this is on 9.6.3, but
>>> it also occurs on 9.3.17
>>
>> Why not cast the arguments? The first and the third argument have to
>> be the same, and the second argument is always int.
>>
>> merlin
>
> I know that I can cast. I'm wondering if it would be possible/desirable to implement automatic casting. Automatic
castingworks already for functions defined to take bigint and you pass in an integer. But not for these functions that
takeany type.
Right. If you've got 2+ types being passed for 'any', which argument
should you get? It's ambiguous, so the type rules into 'any' taking
functions are stricter than for regular functions. Casting behaviors
more complex than they look on the surface and changes to make them
more flexible are typically difficult to make work.
merlin
