Обсуждение: CREATE TABLE LIKE and tablespaces
Hi, I have an application that creates a daily table from a "prototype" table, so something like: CREATE TABLE data_20130226 LIKE data_prototype INCLUDING DEFAULTS INCLUDING CONSTRATINTS INCLUDING INDEXES; It would be really nice to be able to add: INCLUDING TABLESPACE so that the daily table goes into the same table space as the prototype table. I can give a specific "TABLESPACE tablespace" clause, but then my application needs to be aware that data_prototype is not in the default tablespace. Ideally, I'd like the admin to be able to move data_prototype into an alternate tablespace and have all the daily tables be created in that same tablespace. I suppose similar comments apply to INHERITS. Thoughts? Regards, David.
* David F. Skoll wrote: > I have an application that creates a daily table from a "prototype" > table, so something like: > > CREATE TABLE data_20130226 LIKE data_prototype INCLUDING DEFAULTS INCLUDING CONSTRATINTS INCLUDING INDEXES; > > It would be really nice to be able to add: > > INCLUDING TABLESPACE Workaround: CREATE OR REPLACE FUNCTION table_tablespace(p_relname regclass) RETURNS name LANGUAGE sql AS $$ SELECT t.spcname FROM pg_class c JOIN pg_tablespace t ON (c.oid = p_relname AND COALESCE( NULLIF(c.reltablespace, 0), (SELECT dattablespace FROM pg_database WHERE datname = current_database())) = t.oid); $$; -- It is surprisingly difficult to get the tablespace of a table. -- (Note to self: So *that* is what NULLIF() is for!) CREATE OR REPLACE FUNCTION create_table_like(p_old regclass, p_new name) RETURNS regclass LANGUAGE plpgsql AS $$ BEGIN EXECUTE 'CREATE TABLE ' || quote_ident(p_new) || ' (LIKE ' || quote_ident(p_old::name) || ' INCLUDING DEFAULTS ' || ' INCLUDING CONSTRAINTS ' || ' INCLUDING INDEXES) ' || ' TABLESPACE '|| quote_ident(table_tablespace(p_old)) || ';'; RETURN p_new::regclass; END; $$; test=> SELECT create_table_like('data_prototype', 'data_20130226'); create_table_like ------------------- data_20130226 -- Christian