> 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