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

"Oleg Lebedev" <oleg.lebedev@waterford.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