Re: substring odd behavior

"David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> On Thu, Jan 27, 2022 at 7:22 PM Regina Obe <lr(at)pcorp(dot)us> wrote:
>> Is this intentional behavior?
>> -- I can do this
>> SELECT substring('3.2.0' from '[0-9]*\.([0-9]*)\.');
>> -- But can't do this gives error syntax error at or near "from"
>> SELECT pg_catalog.substring('3.2.0' from '[0-9]*\.([0-9]*)\.');

> Generalizing from three examples, it seems the SQL Standard defined
> functions that use keywords cannot be reliably called with a schema prefix.

The syntax with keywords instead of commas is defined in the SQL standard,
and it is defined there without any schema qualification. They seem to
think that "substring" is also a keyword of sorts, and that's how we
implement it.

In short: you can call substring() with the SQL syntax, which is a
special-purpose production that does not involve any schema name,
or you can call it as an ordinary function with ordinary function
notation. You can't mix pieces of those notations.

regards, tom lane