Обсуждение: 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.
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