WgbN0sfV

1CREATE OR REPLACE FUNCTION copy_table(_source_tbl regclass, _target_tbl text)
2 RETURNS bool AS $func$
3DECLARE query_str text; 
4BEGIN
5  query_str = format($fmt$ DROP TABLE IF EXISTS %1$I; CREATE TABLE %1$I AS (TABLE %s); $fmt$, _target_tbl, _source_tbl);
6  EXECUTE query_str;
7  RAISE NOTICE '%', query_str;
8 RETURN True;
9END $func$ LANGUAGE plpgsql;
10	
11=> SELECT copy_table('ex.test', 'ex.test1');
12NOTICE:  table "ex.test1" does not exist, skipping
13NOTICE:   DROP TABLE IF EXISTS "ex.test1"; CREATE TABLE "ex.test1" AS (TABLE ex.test);
14
15=> dt ex.test1
16Did not find any relation named "ex.test1".
17=> dt "ex.test1"
18         List of relations
19 Schema |   Name   | Type  | Owner 
20--------+----------+-------+-------
21 public | ex.test1 | table | 
22(1 row)
23	
24CREATE OR REPLACE FUNCTION copy_table(_source_tbl regclass
25                                    , _target_tbl text
26                                    , _target_schema text = NULL)
27  RETURNS bool AS
28$func$
29DECLARE
30   query_str text; 
31BEGIN
32   IF _target_schema IS NULL THEN               -- no target schema provided ...
33      SELECT c.relnamespace::regnamespace::text -- ... default to schema of input table
34      FROM   pg_class c
35      WHERE  c.oid = _source_tbl
36      INTO  _target_schema;
37   END IF;
38
39   query_str = format('DROP TABLE IF EXISTS %1$I.%2$I;
40                       CREATE TABLE %1$I.%2$I AS (TABLE %3$s);'
41    , _target_schema
42    , _target_tbl
43    , _source_tbl);
44
45   EXECUTE query_str;
46   RAISE NOTICE '%', query_str;
47   RETURN true;
48END
49$func$  LANGUAGE plpgsql;
50	
51SELECT copy_table('myschema1.b2', 'b3', 'myschema2');
52	
53SELECT copy_table('myschema1.b2', 'b3');
54	
55SELECT copy_table('b2', 'b3');