join and dynamic view

Hi folks

is it possible to make a dynamically declare a view based on a table?

I have 3 tables

create table depts (
did character unique not null, -- key
dsdesc character (3), -- short desc
ddesc character varying(40) -- long desc
);
create table staff (
sid int4 not null unique, -- key
sname character varying(40), -- name
);

create table ranks (
rsid int4 not null references staff(sid),
rdid character not null references depts(did),
rrank int4 not null,
primary key (rsid, rdid)
);

copy "depts" from stdin;
O OPS Operations
M MPD Motive Power Dept
\.
copy "staff" from stdin;
1 Rod
2 Jayne
3 Freddie
\.
copy "ranks" from stdin;
1 M 3
2 M 2
2 O 5
3 O 3
\.

Is it possible to now define a view such that it returns:

select * from myview;
sid | Name | OPS | MPD
-----+---------+-----+-----
1 | Rod | | 3
2 | Jayne | 2 | 5
3 | Freddie | 3 |

and if I add another row to depts, that the new row would be included?
--
Gary Stainburn

This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000