From: | Andres Freund <andres(at)anarazel(dot)de> |
---|---|
To: | Rushabh Lathia <rushabh(dot)lathia(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f) |
Date: | 2017-01-30 21:46:12 |
Message-ID: | 20170130214612.x4e6a5lety4ivtra@alap3.anarazel.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
Hi,
On 2017-01-27 17:58:04 +0530, Rushabh Lathia 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".
I think that's correct. Functions in FROM are essentially a shorthand
for ROWS FROM(). And ROWS FROM doesn't allow arbitrary expressions. It
works if you remove the CASE because then it's a valid ROWS FROM
content.
If, I didn't check, that worked previously, I think that was more
accident than intent.
> 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 IMO shouldn't work either due to the CAST. But indeed it does.
> 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.
That's right, because it's not in the targetlist.
Regards,
Andres
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2017-01-30 21:55:56 | Re: Query fails when SRFs are part of FROM clause (Commit id: 69f4b9c85f) |
Previous Message | Tom Lane | 2017-01-30 21:43:25 | Re: Performance improvement for joins where outer side is unique |