Обсуждение: best way to reference tables
I have many different tables that I want to keep track of. So, I thought of a master table with those table names in it. But, to maintain this (suppose a table changes its name, gets dropped) I want to have some kind of referential integrity - the way foreign keys and constraints do. What could I use that would disallow inserting a name into the master table unless another table by that name already exists? And what could ensure that a table would not be renamed or dropped unless the master table is changed? TJ
On 8/9/05 10:31 AM, "TJ O'Donnell" <tjo@acm.org> wrote: > I have many different tables that I want to keep track of. > So, I thought of a master table with those table names in it. > But, to maintain this (suppose a table changes > its name, gets dropped) I want to have some kind of referential > integrity - the way foreign keys and constraints do. > What could I use that would disallow inserting a name into > the master table unless another table by that name already exists? > And what could ensure that a table would not be renamed or dropped > unless the master table is changed? Good idea, but the table already exists. Try: Select * from pg_tables; And see: http://www.postgresql.org/docs/8.0/interactive/catalogs.html Sean
TJ O'Donnell wrote: > I have many different tables that I want to keep track of. > So, I thought of a master table with those table names in it. > But, to maintain this (suppose a table changes > its name, gets dropped) I want to have some kind of referential > integrity - the way foreign keys and constraints do. > What could I use that would disallow inserting a name into > the master table unless another table by that name already exists? > And what could ensure that a table would not be renamed or dropped > unless the master table is changed? You can write your own triggers that would stop you from adding a non-existent table to your master-table. You can't fire triggers on changes to system tables though, so you can't stop someone adding a table. In any case, you presumably want to track ALTER TABLE ADD COLUMN too so you'll need something a little smarter. Have you considered "pg_dump --schema-only" along with suitable version-control software (CVS/Subversion/Arch etc)? Or are you trying to track something specific? -- Richard Huxton Archonet Ltd
> TJ O'Donnell wrote: >> I have many different tables that I want to keep track of. >> So, I thought of a master table with those table names in it. >> But, to maintain this (suppose a table changes >> its name, gets dropped) I want to have some kind of referential >> integrity - the way foreign keys and constraints do. >> What could I use that would disallow inserting a name into >> the master table unless another table by that name already exists? And what could ensure that >> a table would not be renamed or dropped unless the master table is changed? > > You can write your own triggers that would stop you from adding a non-existent table to your > master-table. You can't fire triggers on changes to system tables though, so you can't stop > someone adding a table. > > In any case, you presumably want to track ALTER TABLE ADD COLUMN too so you'll need something a > little smarter. > > Have you considered "pg_dump --schema-only" along with suitable > version-control software (CVS/Subversion/Arch etc)? Or are you trying to track something > specific? > > -- > Richard Huxton > Archonet Ltd My tables are a subset of all the tables - I'm not trying to keep track of everything! So, I think I'll create a schema for the tables I need to keep track of, and create some procedures to properly create/modify tables therein. I don't need to keep track of all the innards of each table - ADD COLUMN, etc. I guess what I'm asking is: Is there a better way to keep track of a table once it's registered in my master table than just to put its name in my master table? Some system oid type thing that stays fixed in spite of renames or other tables mods? TJ
"TJ O'Donnell" <tjo@acm.org> writes:
> I guess what I'm asking is: Is there a better way to keep track of a
> table once it's registered in my master table than just to put its
> name in my master table? Some system oid type thing that stays fixed
> in spite of renames or other tables mods?
Yeah, you can store the pg_class OID of the table, instead of (or in
addition to) its name. This is exactly what the system itself considers
to be the "identity" of the table.
regards, tom lane
On Tue, Aug 09, 2005 at 04:01:33PM -0400, Tom Lane wrote: > "TJ O'Donnell" <tjo@acm.org> writes: > > I guess what I'm asking is: Is there a better way to keep track of a > > table once it's registered in my master table than just to put its > > name in my master table? Some system oid type thing that stays fixed > > in spite of renames or other tables mods? > > Yeah, you can store the pg_class OID of the table, instead of (or in > addition to) its name. This is exactly what the system itself considers > to be the "identity" of the table. Maybe it is possible to use a column of type regclass to store it. Not sure exactly what advantage that would give, but it's an idea. -- Alvaro Herrera (<alvherre[a]alvh.no-ip.org>) "El miedo atento y previsor es la madre de la seguridad" (E. Burke)
Alvaro Herrera <alvherre@alvh.no-ip.org> writes:
> On Tue, Aug 09, 2005 at 04:01:33PM -0400, Tom Lane wrote:
>> Yeah, you can store the pg_class OID of the table,
> Maybe it is possible to use a column of type regclass to store it. Not
> sure exactly what advantage that would give, but it's an idea.
Hmm, that's a cute idea.
regression=# create table tablist (tabid regclass);
CREATE TABLE
regression=# create table mytab(f1 int);
CREATE TABLE
regression=# insert into tablist values ('mytab');
INSERT 0 1
regression=# select * from tablist;
tabid
-------
mytab
(1 row)
regression=# alter table mytab rename to histab;
ALTER TABLE
regression=# select * from tablist;
tabid
--------
histab
(1 row)
regression=# create schema s1;
CREATE SCHEMA
regression=# alter table histab set schema s1;
ALTER TABLE
regression=# select * from tablist;
tabid
-----------
s1.histab
(1 row)
regression=# drop table s1.histab;
DROP TABLE
regression=# select * from tablist;
tabid
-------
82301
(1 row)
regression=#
regards, tom lane
> Alvaro Herrera <alvherre@alvh.no-ip.org> writes: > >>> On Tue, Aug 09, 2005 at 04:01:33PM -0400, Tom Lane wrote: >> >>>>> Yeah, you can store the pg_class OID of the table, > > >>> Maybe it is possible to use a column of type regclass to store it. Not >>> sure exactly what advantage that would give, but it's an idea. That is a great idea! And you examples were very helpful. I think this will get me what I need. Thanks, TJ