Thread: substring odd behavior

substring odd behavior

From

"Regina Obe"

Date:

28 January 2022, 05:22:14

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]*)\.');

-- but can do
SELECT pg_catalog.substring('3.2.0', '[0-9]*\.([0-9]*)\.');


Thanks,
Regina

Re: substring odd behavior

From

"David G. Johnston"

Date:

28 January 2022, 05:54:49

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.

Re: substring odd behavior

From

Tom Lane

Date:

28 January 2022, 06:06:28

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> 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]*)\.');

> Generalizing from three examples, it seems the SQL Standard defined
> functions that use keywords cannot be reliably called with a schema prefix.

The syntax with keywords instead of commas is defined in the SQL standard,
and it is defined there without any schema qualification.  They seem to
think that "substring" is also a keyword of sorts, and that's how we
implement it.

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.

            regards, tom lane

Re: substring odd behavior

From

Mark Dilger

Date:

28 January 2022, 06:11:25


> 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

Re: substring odd behavior

From

Julien Rouhaud

Date:

28 January 2022, 06:12:03

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@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.

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().