I have a need for relation synonyms in PostgreSQL. I don't see it in
7.2.1 but the catalog seems to be able to support it more or less.
Here's what I intend to do:
1) Create a duplicate record in pg_class for the base table information
but with the relname set to the synonym name.
2) Duplicate the attribute information in pg_attribute for the base
table but with the attrelid set to the synonym oid.
(see test SQL below)
Is there anything fundamentally wrong with this approach? In particular,
could this concievably break anything. I do understand that it's not a
perfect approach since the attributes are not dynamic in so far as any
changes made to the base table. However, it does appear to provide a
superior solution than using a view with a full set of rules. That said,
is there a safe way of creating a "true" duplicate record in pg_class
(including the oid) so that a "true" synonym could be created?
Here's the testing I did:
insert into pg_class
select 'syn_test', reltype, relowner, relam, relfilenode, relpages,
reltuples, reltoastrelid, reltoastidxid,
relhasindex, relisshared, relkind, relnatts, relchecks, reltriggers,
relukeys, relfkeys,
relrefs, relhasoids, relhaspkey, relhasrules, relhassubclass, relacl
from pg_class where lower(relname) = lower('tbl_test')
;
insert into pg_attribute
select c2.oid, attname, atttypid, attstattarget, attlen, attnum,
attndims, attcacheoff, atttypmod, attbyval,
attstorage, attisset, attalign, attnotnull, atthasdef
from pg_class c1, pg_class c2, pg_attribute a1
where attrelid = c1.oid
and lower(c1.relname) = lower('tbl_test')
and lower(c2.relname) = lower('syn_test')
;
select * from tbl_test; (no problems)
select * from syn_test; (no problems)
delete from pg_attribute
where attrelid = (select oid from pg_class where lower(relname) =
lower('syn_test'))
;
delete from pg_class
where lower(relname) = lower('syn_test')
;
Thanks!
Marc L.
