Re: PostgreSQL 9.3.5 substring(text from pattern for escape) bug

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

>>> What appears to be happening is that the _Q? on the tail_ is being
>>> matched, but _the Q is also being included in the collected data.__

>> You seem to expect that % must be non-greedy and let the final Q?
>> match 1 Q instead of 0, but there doesn't appear to be anything in
>> the doc that supports this interpretation. In fact, it mentions that
>> "%" is comparable to ".*" in POSIX regular expressions, and the
>> latter _is_ greedy.

Tom> Right. You could get the behavior you want using a non-greedy
Tom> quantifier, but you'd have to use the POSIX regexp functions, not
Tom> substring().

I looked up the spec on this point. As far as I can see, we're not
following it, but neither does the spec do what the OP wanted; in fact
the result should have included the _leading_ Q as well as the trailing
one.

The relevant part of SQL2016 seems to be this:

6.32 <string value function>

General Rules

6) If <regular expression substring function> is specified, then:

[...rules that split the pattern into 'R1#"R2#"R3' omitted...]

h) Otherwise, the result S of the <regular expression substring
function> is computed as follows:

i) Let S1 be the shortest initial substring of C such that there is
a substring S23 of C such that the value of the following <search
condition> is True:

'C' = 'S1' || 'S23' AND
'S1' SIMILAR TO 'R1' ESCAPE 'E' AND
'S23' SIMILAR TO '(R2R3)' ESCAPE 'E'

ii) Let S3 be the shortest final substring of S23 such that there is
a substring S2 of S23 such that the value of the following
<search condition> is True:

'S23' = 'S2' || 'S3' AND
'S2' SIMILAR TO 'R2' ESCAPE 'E' AND
'S3' SIMILAR TO 'R3' ESCAPE 'E'

iii) The result of the <regular expression substring function> is S2.

--
Andrew (irc:RhodiumToad)