Martijn van Oosterhout <kleptog@svana.org> writes:
> It's even sillier than that:
> test=# SELECT substring ('1234567890' FOR 4::bigint);
> substring
> -----------
>
> (1 row)
> test=# SELECT substring ('1234567890' FOR 4::int);
> substring
> -----------
> 1234
> (1 row)
This has been complained of before. The problem is that there is no
implicit cast from bigint to int, but there is one from bigint to text,
so the only acceptable mapping the parser can find is to convert bigint
to text and apply the pattern-match version of substring(). (There are
some other things happening here because of the weird SQL99 syntax, but
that's the bottom line.)
I have opined before that implicit cross-category casts to text are
evil. Unfortunately, we'll probably break a lot of people's
applications if we remove them...
regards, tom lane