Gregory Stark <stark@enterprisedb.com> writes:
> This seems odd. It's not deciding that it's ambiguous or coming from another
> datatype for which no implicit cast exists. It knows perfectly well that it
> wants to convert to text but fails?
> postgres=# select 'a'||b from (select 'b' as b) as x;
> ERROR: failed to find conversion function from unknown to text
No, it's always done that. The problem is that we don't force the
output of the subselect to be non-unknown before freezing our
determination of what it is --- once we start analysis of the upper
query it's too late to change our minds about what the subquery
produces.
This has been complained of before, eg
http://archives.postgresql.org/pgsql-bugs/2007-05/msg00000.php
At the time I was thinking of forcing unknown result columns to text
before exiting parse analysis of the subquery. I thought then, and
still think, that that would have a nontrivial risk of breaking
cases that historically have worked.
Reconsidering it now, I notice there's a kluge in transformInsertStmt()
for a related case in INSERT ... SELECT, viz copy up the unknown-type
Const as a Const instead of a reference to the subquery output. Perhaps
it'd be a good idea to try to do something similar for cases like this.
You'd have to worry about outer joins and so on, though.
regards, tom lane