Re: substring odd behavior

Hi,

On Thu, Jan 27, 2022 at 07:54:49PM -0700, David G. Johnston wrote:
> 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]*)\.');
> >
> >
> select pg_catalog.trim(leading 'hi' from 'hi david'); -- syntax error at or
> near "leading" (returns ' david' if pg_catalog is omitted). I also tested
> position(text in text) and get a syntax failure at the second text argument.
>
> Generalizing from three examples, it seems the SQL Standard defined
> functions that use keywords cannot be reliably called with a schema prefix.

Yes, I don't have a copy of the standard but I think that they define such
constructs as part of the language and not plain function calls, so you can't
schema qualify it.

That's how it's internally implemented, and the SUBSTRING( FOR / FROM / ESCAPE
) is a syntactic sugar over pg_catalog.substring().