Hi All.
I would like to know how people emulates right/left outer joins in
postgres?
Namely, I would like to get list of tables from postgres with parent
class for every table if one exists and null if parent class doesn't
exists, e.g.
TABLE | PARENT
-------------------
table1 |
table2 |
table3 | table1
table4 | table2
.............
I tried
select distinct a.relname, c.relname
from pg_class a, pg_inherits b, pg_class c
where a.relkind='r' and a.relname !~~ 'pg_%' and
(b.inhrel=a.oid ornot exists (select inhrel from pg_inherits where inhrel=a.oid))
and
c.oid=b.inhparent;
but I get each table wich hasn't parent more than one time.
Following query works pretty well:
select a.relname, c.relname
from pg_class a, pg_inherits b, pg_class c
where a.relkind='r' and a.relname !~~ 'pg_%' and
b.inhrel=a.oid and c.oid=b.inhparent
union
select a.relname, ''
from pg_class a
where a.relkind='r' and a.relname !~~ 'pg_%' and
not exists (select inhrel from pg_inherits where inhrel=a.oid);
but since it's union I can't order result on my desire.
Perhaps anybody knows more simple way to do this?
And generally which methods exists for outer joins substitution?
Sincerely yours, Yury.
don.web-page.net, ICQ 11831432
