Marko Kreen <marko@l-t.ee> writes:
> # select o.id from op o union all SELECT -1 order by o.id;
> ERROR: Relation 'o' does not exist
>>
>> This is not valid SQL. For one thing, the table alias "o" is not
>> visible outside the first component SELECT.
>>
>> Yes, I know 7.0 took it... but its handling of ORDER BY on UNION
>> was pretty darn broken.
> Doh. But if I have several tables with a field 'id'? Then only
> way is to use the column number?
You could assign column names:
SELECT o.id as id1, p.id as id2, ... UNION ... ORDER BY id1, id2;
> Anyway such stuff should be documented I guess. From current
> docs I read that it should work.
Where?
> I would have expected that one
> of the select's aliases would be transferred to ORDER BY but its
> not possible?
The first subselect's column names are transferred to ORDER BY.
regards, tom lane
