Re: substring odd behavior

> On Jan 27, 2022, at 7:06 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> 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.

Beware that your choice of grammar interacts with search_path considerations.  If you use what looks like a regular
functioncall, the search_path will be consulted, but if you use the "from" based syntax, the one from pg_catalog will
beused: 

SET search_path = substr_test, pg_catalog;
SELECT substring('first', 'second');
         substring
----------------------------
 substr_test: first, second
(1 row)

SELECT substring('first' FROM 'second');
 substring
-----------

(1 row)

SET search_path = pg_catalog, substr_test;
SELECT substring('first', 'second');
 substring
-----------

(1 row)

SELECT substring('first' FROM 'second');
 substring
-----------

(1 row)

SELECT substr_test.substring('first', 'second');
         substring
----------------------------
 substr_test: first, second
(1 row)

SELECT substr_test.substring('first' FROM 'second');
ERROR:  syntax error at or near "FROM"
LINE 1: SELECT substr_test.substring('first' FROM 'second');


—
Mark Dilger
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company