Re: unexpected update behavior with temp tables

On Jul 8, 2004, at 9:14 AM, Stephan Szabo wrote:

>
> On Thu, 8 Jul 2004, Timothy Perrigo wrote:
>
>> OPT=# select id as not_id, b into temp temp_foo from foo where b =
>> 'Tim';
>> SELECT
>> OPT=# select * from temp_foo;
>> not_id | b
>> --------+-----
>> 1 | Tim
>> (1 row)
>>
>> OPT=# update foo set b = 'Timothy' where id in (select id from
>> temp_foo);
>
> Subselects like that are AFAIK allowed to see outer columns according
> to
> the SQL spec. Thus, the id inside the subselect is effectively foo.id.
>
> This behavior is useful when you want to do something like a function
> or
> operator on an inner column and an outer column inside the subselect
> and
> painful in cases like this where effectively the clause becomes "id is
> not
> null" which for a primary key is itself a long way of saying "true".
>
>
Thanks for the reply, Stephan. I guess I can see the rationale for
this, though it is quite easy to cause yourself quite a bit of grief.
It would certainly make things safer if columns in the subselect which
refer to columns in the table from the outer query where required to be
fully specified (i.e. "foo.id", instead of just "id"), but if this
behavior is part of the standard, I imagine there's little chance of
changing it...

I appreciate the assistance!

Tim