best way to reference tables

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