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

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. The ExecMakeFunctionResult() call for the cast function
then decides that the function's argument expression doesn't return a
set, so it changes the node execution pointer so that subsequent
executions go through the much simpler ExecMakeFunctionResultNoSets()
execution function. And then that spits up when on the next row, the
argument expression *does* return a set :-(

You could work around that using the trick documented in the
regexp_matches documentation to force it to return exactly one row,
ie interpose a sub-SELECT:

regression=# SELECT *, CASE WHEN id = 2 THEN
(select (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);
id | input_string | o_l2_a
----+------------------+------------------
1 | |
2 | 0000000049404 | 49404
3 | FROM 10000000876 | FROM 10000000876
(3 rows)

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