CREATE SYNONYM suggestions

Поиск
Список
Период
Сортировка
От Marc Lavergne
Тема CREATE SYNONYM suggestions
Дата
Msg-id 3D3E47AF.1010709@richlava.com
обсуждение исходный текст
Ответы Re: CREATE SYNONYM suggestions  (Rod Taylor <rbt@zort.ca>)
Re: CREATE SYNONYM suggestions  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-hackers
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.



В списке pgsql-hackers по дате отправления:

Предыдущее
От: Marc Lavergne
Дата:
Сообщение: Re: [PATCHES] prepareable statements
Следующее
От: "J. R. Nield"
Дата:
Сообщение: PITR, checkpoint, and local relations