david.g.johns...@gmail.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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs