self outer join

Hi,

In pg 7.1 using the new outer join syntax.

SELECT *
FROM t1 LEFT OUTER JOIN t2 ON (t1.col = t2.col);

or

SELECT *
FROM t1 LEFT OUTER JOIN t2 USING (col);

How does one specify an alias for the table being joined. This is
important if you are creating an outer join to the same table:

Oracle syntax (simplified) would be:

select r.key,
r.rank,
r2.rank as last_weeks_rank
from rank r,
rank r2
where r.key = (+)r2.key and
r2.week = r1.week - 1
;

This is quite painful to do using the older outer join technique with
the UNION ALL ... (unless someone can do this better):

select t.upckey, r.rank, r2.rank as last_weeks_ranking
from title t,
rank r,
rank r2
where r.upckey = t.upckey and
r2.upckey = t.upckey and
r.week = 200102 and
r2.week = r.week-1 and
r.media = 'M' and
r2.media = 'M'
UNION ALL
select t.upckey, r.rank, null as last_weeks_ranking
from title t,
rank r
where r.upckey = t.upckey and
r.week = 200102 and
r.media = 'M' and
not exists (select r2.week
from rank r2
where r2.upckey = t.upckey and
r2.week = r.week - 1 and
r2.media = 'M')
order by
r.rank
;

Phewy. If that's it, then I'll do it programmatically with cursors.

Thanks for any and all help on this
-David