Re: CREATE TABLE LIKE and tablespaces

* 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