CAST(CASE WHEN pc.contype IN ('f','u','p') THEN generate_series(1, array_upper(pc.conkey, 1)) ELSE NULL END AS int) AS position;
Above query is failing with "set-valued function called in context that cannot
accept a set". But if I remove the CASE from the query then it working just good.
Like:
SELECT *
FROM pg_constraint pc,
CAST(generate_series(1, array_upper(pc.conkey, 1)) AS int) AS position;
This started failing with 69f4b9c85f168ae006929eec44fc44d569e846b9. It seems
check_srf_call_placement() sets the hasTargetSRFs flag and but when the SRFs
at the rtable ofcourse this flag doesn't get set. It seems like missing something
their, but I might be completely wrong as not quire aware of this area.
I'm a bit surprised that your query actually works...and without delving into source code its hard to explain why it should/shouldn't or whether the recent SRF work was intended to impact it.
In any case the more idiomatic way of writing your query these days (since 9.4 came out) is:
SELECT *
FROM pg_constraint pc
LEFT JOIN LATERAL generate_series(1, case when contype in ('f','p','u') then array_upper(pc.conkey, 1) else 0 end) gs ON true;
generate_series is smart enough to return an empty set (instead of erroring out) when provided with (1,0) as arguments.
Thanks for the providing work-around query and I also understood your point.
At the same time reason to raise this issue was, because this was working before
69f4b9c85f168ae006929eec44fc44d569e846b9 commit and now its throwing
an error. So whether its intended or query started failing because of some
bug introduced with the commit.
Issues is reproducible when query re-written with LEFT JOIN LATERAL and I
continue to use CASE statement.
SELECT *
FROM pg_constraint pc
LEFT JOIN LATERAL CAST((CASE WHEN pc.contype IN ('f','u','p') THEN generate_series(1, array_upper(pc.conkey, 1)) ELSE NULL END) AS int) gs ON true;
ERROR: set-valued function called in context that cannot accept a set