Re: substring odd behavior

> On Jan 27, 2022, at 7:06 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)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 function call, the search_path will be consulted, but if you use the "from" based syntax, the one from pg_catalog will be used:

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