Thread: update substring pattern matching syntax

At 
<https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_Standard#Obsolete_syntax_for_substring.28.29> 
it is described that the substring pattern matching syntax in PostgreSQL 
does not conform to the current standard.  PostgreSQL implements

     SUBSTRING(text FROM pattern FOR escapechar)

whereas the current standard says

     SUBSTRING(text SIMILAR pattern ESCAPE escapechar)

The former was in SQL99, but the latter has been there since SQL:2003.

It's pretty easy to implement the second form also, so here is a patch 
that does that.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 6/19/20 11:42 AM, Peter Eisentraut wrote:
> At
> <https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_Standard#Obsolete_syntax_for_substring.28.29>
> it is described that the substring pattern matching syntax in PostgreSQL
> does not conform to the current standard.  PostgreSQL implements
> 
>     SUBSTRING(text FROM pattern FOR escapechar)
> 
> whereas the current standard says
> 
>     SUBSTRING(text SIMILAR pattern ESCAPE escapechar)
> 
> The former was in SQL99, but the latter has been there since SQL:2003.
> 
> It's pretty easy to implement the second form also, so here is a patch
> that does that.


Oh good, this was on my list (I added that item to the wiki).

The patches look straightforward to me.  The grammar cleanup patch makes
things easier to read indeed.  At first I didn't see a test left over
for the old syntax, but it's there so this is all LGTM.

Thanks for doing this!
-- 
Vik Fearing

Hello Peter,

> whereas the current standard says
>
>    SUBSTRING(text SIMILAR pattern ESCAPE escapechar)
>
> The former was in SQL99, but the latter has been there since SQL:2003.
>
> It's pretty easy to implement the second form also, so here is a patch that 
> does that.

Patches apply cleanly, compile and "make check" is ok. doc gen is ok as 
well.

Grammar cleanup is a definite improvement as it makes the grammar closer 
to the actual syntax.

I cannot say I'm a fan of this kind of keywords added for some arguments. 
I guess that it allows distinguishing between variants. I do not have the 
standard at hand: I wanted to check whether these keywords could be 
reordered, i.e. whether SUBSTRING(text ESCAPE ec SIMILAR part) was legal. 
I guess not.

Maybe the doc could advertise more systematically whether a features 
conforms fully or partially to some SQL standards, or is pg specific. The 
added documentation refers both to SQL:1999 and SQL99. I'd suggest to 
chose one, possibly the former, and use it everywhere consistently.

It seems that two instances where not updated to the new syntax, see in 
./src/backend/catalog/information_schema.sql and 
./contrib/citext/sql/citext.sql.

-- 
Fabien.

On 2020-06-20 09:08, Fabien COELHO wrote:
> I cannot say I'm a fan of this kind of keywords added for some arguments.
> I guess that it allows distinguishing between variants. I do not have the
> standard at hand: I wanted to check whether these keywords could be
> reordered, i.e. whether SUBSTRING(text ESCAPE ec SIMILAR part) was legal.
> I guess not.

It is not.

> Maybe the doc could advertise more systematically whether a features
> conforms fully or partially to some SQL standards, or is pg specific.

I think that would be useful, but it's probably a broader topic than 
just for this specific function.

> The
> added documentation refers both to SQL:1999 and SQL99. I'd suggest to
> chose one, possibly the former, and use it everywhere consistently.

fixed

> It seems that two instances where not updated to the new syntax, see in
> ./src/backend/catalog/information_schema.sql and
> ./contrib/citext/sql/citext.sql.

done

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Hallo Peter,

v2 patches apply cleanly, compile, global check ok, citext check ok, doc 
gen ok. No further comments.

As I did not find an entry in the CF, so I did nothing about tagging it 
"ready".

-- 
Fabien.

On 2020-06-28 08:13, Fabien COELHO wrote:
> v2 patches apply cleanly, compile, global check ok, citext check ok, doc
> gen ok. No further comments.

committed, thanks

> As I did not find an entry in the CF, so I did nothing about tagging it
> "ready".

Right, I had not registered it yet.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services