CREATE TABLE LIKE and tablespaces

[David F. Skoll]

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.

[Christian Ullrich]

Post by David F. Skoll
I have an application that creates a daily table from a "prototype"
CREATE TABLE data_20130226 LIKE data_prototype INCLUDING DEFAULTS INCLUDING CONSTRATINTS INCLUDING INDEXES;
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