Re: BUG #8228: Unexpected "set-valued function" with varchar(n) but not varchar

On Thu, Jun 13, 2013 at 4:02 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> david(dot)g(dot)johnston(at)gmail(dot)com writes:
> > The following query results in "SQL Error: ERROR: set-valued function
> called
> > in context that cannot accept a set"
>
> > SELECT *, CASE WHEN id = 2 THEN
> > (regexp_matches(input_string,'^0*([1-9]\d+)$'))[1] ELSE input_string
> > END::varchar(30) AS o_l2_a
> > FROM (
> > VALUES (1,''), (2,'0000000049404'),(3,'FROM 10000000876')
> > ) l0_src (id, input_string)
>
> Hm, interesting example. What seems to be happening is that during
> evaluation of the SELECT list for the first VALUES row, the CASE
> expression doesn't call regexp_matches() but just returns the ELSE
> expression.
>

Does all this explain why it DOES work if the cast on the END is a plain
"varchar"?

>
> Not sure about non-hack fixes. I guess we need to analyze
> can-it-return-a-set statically instead of believing the first execution
> result, but that might add an unpleasant amount of startup overhead.
>
> regards, tom lane
>

The issue with the regexp_matches call generally is that absence of a "g"
modifier means that the set-returning function will never return a set. It
would seem to make more sense to not make that a modifier but instead have
one function defined to return a set (i.e., the current definition) and
another one defined to return a simply text[]. This would make using the
call in a scalar context easier. Is there any reason why a UDF defined as
such would have a problem? The set-returning one accepting the parameter
is nice since you can toggle global/single within the same query - but in
many use-cases only the single-match mode is desired.

Are there any other functions that have this same risk profile that would
increase the applicability of such a patch?

David J.