update substring pattern matching syntax

pá 19. 6. 2020 v 11:42 odesílatel Peter Eisentraut <
peter.eisentraut@2ndquadrant.com> napsal:

At
<
https://wiki.postgresql.org/wiki/PostgreSQL_vs_SQL_Standard#Obsolete_syntax_for_substring.28.29&gt;

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

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&gt;
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