update substring pattern matching syntax

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

update substring pattern matching syntax

Peter Eisentraut-6
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

0001-Clean-up-grammar-a-bit.patch (6K) Download Attachment
0002-Add-current-substring-regular-expression-syntax.patch (14K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: update substring pattern matching syntax

Pavel Stehule


pá 19. 6. 2020 v 11:42 odesílatel Peter Eisentraut <[hidden email]> napsal:
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.

+1

Pavel


--
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
Reply | Threaded
Open this post in threaded view
|

Re: update substring pattern matching syntax

Vik Fearing-6
In reply to this post by Peter Eisentraut-6
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


Reply | Threaded
Open this post in threaded view
|

Re: update substring pattern matching syntax

Fabien COELHO-3
In reply to this post by Peter Eisentraut-6

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.


Reply | Threaded
Open this post in threaded view
|

Re: update substring pattern matching syntax

Peter Eisentraut-6
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

v2-0001-Clean-up-grammar-a-bit.patch (6K) Download Attachment
v2-0002-Add-current-substring-regular-expression-syntax.patch (17K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: update substring pattern matching syntax

Fabien COELHO-3

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.


Reply | Threaded
Open this post in threaded view
|

Re: update substring pattern matching syntax

Peter Eisentraut-6
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