Re: [HACKERS] subselect bug (was Re: DBLink: interesting issue)

"Oleg Lebedev" <oleg(dot)lebedev(at)waterford(dot)org> writes:
> However, I am still getting an error when running the following UPDATE
> statement:
> UPDATE tablea
> SET objectid=a1.objectid,
> objectversion=a1.objectversion,
> val=a1.val,
> var=a1.var
> FROM (
> SELECT *
> FROM dblink(' hostaddr=12.34.5.6 port=5433
> dbname=webspec user=user password=pass',
> 'SELECT objectid, objectversion, val, var
> FROM tablea
> WHERE objectid=' || tablea.objectid ||
> ' AND objectversion<' || tablea.objectversion)
> AS dblink_rec(objectid int8, objectversion int4,
> val int4, var varchar)
> ) a1;
> ERROR: FROM function expression may not refer to other relations of
> same query level

> I think a similar bug needs to be fixed for FromExpr case.

No, I think this is correct. You have a FROM item (the a1 subselect)
that is trying to refer to the values of another FROM item --- the
target table of the UPDATE. Consider the structurally-equivalent

SELECT * FROM
tablea,
(SELECT * FROM dblink(... tablea.objectid ...) ...) a1;

This is not meaningful because each FROM expression has to be
independent.

You could probably make it work with something along the lines of

UPDATE tablea
SET objectid=a1.objectid,
objectversion=a1.objectversion,
val=a1.val,
var=a1.var
FROM (
SELECT *
FROM dblink(' hostaddr=12.34.5.6 port=5433
dbname=webspec user=user password=pass',
'SELECT objectid, objectversion, val, var
FROM tablea')
AS dblink_rec(objectid int8, objectversion int4,
val int4, var varchar)
) a1
WHERE a1.objectid=tablea.objectid
AND a1.objectversion<tablea.objectversion;

ie, pull the join conditions out into the outer WHERE clause, where
they're supposed to be...

regards, tom lane