Propose: enum with all registered table names?
Hello.
Sometimes I want to save in a table reference to another table's name.
E.g. I am creating an accounting system which (on triggers) logs which
record is changed and in which table:
CREATE TABLE log(
tbl XXX,
id INTEGER,
blahblah
);
Nowadays XXX may be:
1) Table name. But it is quite inefficient by disk usage. Another bad thing:
if I rename a table, I have to rename all rows in log table.
2) Table OID. It is very bad for pg_dump: after the restoration log table
will be unusable.
3) ENUM with all table names. But I have to add an element to ENUM each time
I create a new table, and, if I rename a table, I have to rename an ENUM
element too.
So it would be very useful if Postgres has a special, system ENUM (e.g.
pg_catalog.table_names_enum) which holds names of all tables in the database
(format: schema.table), and their elements are automatically renamed when a
table is renamed.
Dmitry Koterov wrote:
So it would be very useful if Postgres has a special, system ENUM (e.g.
pg_catalog.table_names_enum) which holds names of all tables in the database
(format: schema.table), and their elements are automatically renamed when a
table is renamed.
Too late :-)
It's regclass you're after I think.
=> CREATE TABLE track_tables (t regclass);
=> INSERT INTO track_tables (t) VALUES ('"A"'::regclass);
INSERT 0 1
=> SELECT * FROM track_tables;
t
-----
"A"
(1 row)
=> ALTER TABLE "A" RENAME TO atable;
ALTER TABLE
=> SELECT * FROM track_tables;
t
--------
atable
(1 row)
--
Richard Huxton
Archonet Ltd
Dmitry Koterov wrote:
CREATE TABLE log(
tbl XXX,
id INTEGER,
blahblah
);
Use type regclass.
--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Richard Huxton <dev@archonet.com> writes:
It's regclass you're after I think.
A fairly large problem with either regclass or a hypothetical
system-maintained enum is that a table with such a column effectively
has a data dependency on all the tables it mentions. Which pg_dump
won't know about, so it might try to restore that table before it's
finished creating all the mentioned tables.
I think this will actually work alright in existing pg_dump releases
because it does all the table creation DDL before trying to load any
data. You might have some problems with future parallel pg_restores
though.
On the whole I'd recommend staying away from the idea. It doesn't
seem valuable enough to be worth taking risks over.
regards, tom lane