From: Christian Ullrich <chris@chrullrich.net>
To: pgsql-admin@postgresql.org
Subject: Re: CREATE TABLE LIKE and tablespaces
Date: Sat, 02 Mar 2013 19:40:42 +0100
Message-ID: <kgth37$aup$1@ger.gmane.org> (raw)
In-Reply-To: <20130227132935.44fc25f1@hydrogen.roaringpenguin.com>
References: <20130227132935.44fc25f1@hydrogen.roaringpenguin.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-admin>
* 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
--
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: chris@chrullrich.net
Subject: Re: CREATE TABLE LIKE and tablespaces
In-Reply-To: <kgth37$aup$1@ger.gmane.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox