G'day all,

PG version: 8.1.4 (also 7.4.13)
OS: Linux (debian/testing)

In an update, using a sub-select from a table with the same name
but in a different schema results in an error. The same thing
works if you alias the sub-select table or if you qualify the
schema of the update table.

See script below.

I'm not sure if this is a bug or if it's displaying my ignorance
of this corner of SQL...

Cheers,

Chris.

----------------------------------------------------------------------
create table a (id integer, name text);
insert into a values ('1', 'thomas');
insert into a values ('2', 'edward');

create schema temp;
create table temp.a (id integer, name text);
insert into temp.a values ('1', 'tom');
insert into temp.a values ('2', 'eddie');

--
-- fails with:
-- ERROR: more than one row returned by a subquery used as an expression
--
update a set name = (
select name
from temp.a
where temp.a.id = a.id
)

--
-- same thing with schema-qualified update table: works as expected
--
update public.a set name = (
select name
from temp.a
where temp.a.id = public.a.id
);

--
-- same thing but using an alias: works as expected
--
update a set name = (
select name
from temp.a foo
where foo.id = a.id
);
----------------------------------------------------------------------

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly