Re: SQL-spec incompatibilities in similar_escape() and related stuff

>>>>> "Tom" == Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

Tom> but in recent versions it's

Tom> <regular expression substring function> ::=
Tom> SUBSTRING <left paren> <character value expression>
Tom> SIMILAR <character value expression>
Tom> ESCAPE <escape character> <right paren>

Tom> I am, frankly, inclined to ignore this as a bad idea. We do have
Tom> SIMILAR and ESCAPE as keywords already, but they're
Tom> type_func_name_keyword and unreserved_keyword respectively. To
Tom> support this syntax, I'm pretty sure we'd have to make them both
Tom> fully reserved.

I only did a quick trial but it doesn't seem to require reserving them
more strictly - just adding the obvious productions to the grammar
doesn't introduce any conflicts.

Tom> * Our function similar_escape() is not documented, but it
Tom> underlies three things in the grammar:

Tom> a SIMILAR TO b
Tom> Translated as "a ~ similar_escape(b, null)"

Tom> a SIMILAR TO b ESCAPE e
Tom> Translated as "a ~ similar_escape(b, e)"

Tom> substring(a, b, e)
Tom> This is a SQL function expanding to
Tom> select pg_catalog.substring($1, pg_catalog.similar_escape($2, $3))

Tom> To support the first usage, similar_escape is non-strict, and it
Tom> takes a NULL second argument to mean '\'. This is already a SQL
Tom> spec violation, because as far as I can tell from the spec, if you
Tom> don't write an ESCAPE clause then there is *no* escape character;
Tom> there certainly is not a default of '\'. However, we document this
Tom> behavior, so I don't know if we want to change it.

This is the same spec violation that we also have for LIKE, which also
is supposed to have no escape character in the absense of an explicit
ESCAPE clause.

--
Andrew (irc:RhodiumToad)