On Fri, Jan 27, 2017 at 5:28 AM, Rushabh Lathia <rushabh.lat...@gmail.com> wrote:
> Consider the below test; > > CREATE TABLE tab ( a int primary key); > > SELECT * > FROM pg_constraint pc, > 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. David J.