pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  

help / color / mirror / Atom feed

CREATE TABLE LIKE and tablespaces
2+ messages / 2 participants
[nested] [flat]

* CREATE TABLE LIKE and tablespaces
@ 2013-02-27 18:29  David F. Skoll <dfs@roaringpenguin.com>
  0 siblings, 1 reply; 2+ messages in thread

From: David F. Skoll @ 2013-02-27 18:29 UTC (permalink / raw)
  To: pgsql-admin

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.



-- 
Sent via pgsql-admin mailing list (pgsql-admin@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: CREATE TABLE LIKE and tablespaces
@ 2013-03-02 18:40  Christian Ullrich <chris@chrullrich.net>
  parent: David F. Skoll <dfs@roaringpenguin.com>
  0 siblings, 0 replies; 2+ messages in thread

From: Christian Ullrich @ 2013-03-02 18:40 UTC (permalink / raw)
  To: pgsql-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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

end of thread, other threads:[~2013-03-02 18:40 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2013-02-27 18:29 CREATE TABLE LIKE and tablespaces David F. Skoll <dfs@roaringpenguin.com>
2013-03-02 18:40 ` Christian Ullrich <chris@chrullrich.net>

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