Re: substring odd behavior - Mailing list pgsql-hackers

From David G. Johnston
Subject Re: substring odd behavior
Date
Msg-id CAKFQuwYBg-C=M78amxi8mE39s=KL+V6Kf2wSKb2j7Q5dMnZY=A@mail.gmail.com
Whole thread Raw
In response to substring odd behavior  ("Regina Obe" <lr@pcorp.us>)
Responses Re: substring odd behavior
Re: substring odd behavior
List pgsql-hackers
On Thu, Jan 27, 2022 at 7:22 PM Regina Obe <lr@pcorp.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.

This seems likely to be intended (or at least not worth avoiding) behavior given the special constraints these functions place on the parser.  But I expect someone more authoritative than I on the subject to chime in.  For me, adding another sentence to Chapter 9.4 (I don't know if other categories of functions have this dynamic) after we say "SQL defines some string functions that use key words...PostgreSQL also provides versions of these functions that use the regular function invocation syntax."  The schema qualification would seem to be part of "regular function invocation syntax" only.

I'd consider adding "Note that the ability to specify the pg_catalog schema in front of the function name only applies to the regular function invocation syntax."  Though it isn't like our users are tripping over this either.  Maybe noting it in the Syntax chapter would be more appropriate.  I'd rather not leave the status quo behavior without documenting it somewhere (but all the better if it can be fixed).

It probably isn't worth adding a section to the Syntax chapter for "Irregular Function Calls (SQL Standard Mandated)" but I'd entertain the thought.

David J.

pgsql-hackers by date:

Previous
From: Bharath Rupireddy
Date:
Subject: Re: Is there a way (except from server logs) to know the kind of on-going/last checkpoint?
Next
From: Thomas Munro
Date:
Subject: Re: Creation of an empty table is not fsync'd at checkpoint