Re: join_references: variable not in subplan target lists

Peter Eisentraut <peter_e(at)gmx(dot)net> writes:
> The cited error message appears when loading the attached file (a cut-down
> version of a local development version of the information schema)

I've been able to reduce the problem to this test case:

drop view x1;

CREATE VIEW x1 AS
SELECT 1
FROM pg_type t, pg_proc p
WHERE
t.oid = p.prorettype AND
p.pronamespace IN
(SELECT n.nspname
FROM pg_namespace n, pg_proc p2
WHERE n.oid = p2.pronamespace);

select * from x1;

While I now need to burrow into the IN-as-join code and find out where
it's dropping the ball, I don't think this need stop you from making
progress on the information schema. The reason the problem is appearing
seems to be the implied cast that's getting introduced in the IN
comparison, because pronamespace (an OID) isn't directly comparable
to nspname (a NAME). (They're both getting coerced to TEXT, which once
again points up my opinion that we are way too loose with implicit
coercions to TEXT, but never mind that right now.) In short, the bug is
being triggered only because you're comparing the wrong pair of columns,
and so you'll need to change the query anyway.

regards, tom lane