github.com/tuhaihe/gpbackup@v1.0.3/end_to_end/resources/gpdb4_objects.sql (about)

     1  
     2  SET statement_timeout = 0;
     3  SET client_encoding = 'UTF8';
     4  SET standard_conforming_strings = on;
     5  SET check_function_bodies = false;
     6  SET client_min_messages = warning;
     7  
     8  SET default_with_oids = false;
     9  
    10  
    11  CREATE SCHEMA schema2;
    12  
    13  
    14  SET search_path = public, pg_catalog;
    15  
    16  
    17  CREATE TYPE base_type;
    18  
    19  
    20  
    21  CREATE FUNCTION base_fn_in(cstring) RETURNS base_type
    22      AS $$boolin$$
    23      LANGUAGE internal NO SQL;
    24  
    25  
    26  
    27  
    28  CREATE FUNCTION base_fn_out(base_type) RETURNS cstring
    29      AS $$boolout$$
    30      LANGUAGE internal NO SQL;
    31  
    32  
    33  CREATE TYPE base_type (
    34      INTERNALLENGTH = variable,
    35      INPUT = base_fn_in,
    36      OUTPUT = base_fn_out,
    37      ALIGNMENT = int4,
    38      STORAGE = plain
    39  );
    40  
    41  
    42  CREATE TYPE composite_type AS (
    43  	name integer,
    44  	name1 integer,
    45  	name2 text
    46  );
    47  
    48  
    49  CREATE FUNCTION casttoint(text) RETURNS integer
    50      AS $_$
    51  SELECT cast($1 as integer);
    52  $_$
    53      LANGUAGE sql IMMUTABLE STRICT CONTAINS SQL;
    54  
    55  
    56  CREATE FUNCTION dup(integer, OUT f1 integer, OUT f2 text) RETURNS record
    57      AS $_$
    58  SELECT $1, CAST($1 AS text) || ' is text'
    59  $_$
    60      LANGUAGE sql CONTAINS SQL;
    61  
    62  
    63  CREATE FUNCTION mypre_accum(numeric, numeric) RETURNS numeric
    64      AS $_$
    65  select $1 + $2
    66  $_$
    67      LANGUAGE sql IMMUTABLE STRICT CONTAINS SQL;
    68  
    69  
    70  CREATE FUNCTION mysfunc_accum(numeric, numeric, numeric) RETURNS numeric
    71      AS $_$
    72  select $1 + $2 + $3
    73  $_$
    74      LANGUAGE sql IMMUTABLE STRICT CONTAINS SQL;
    75  
    76  
    77  CREATE FUNCTION plusone(x text) RETURNS text
    78      AS $$
    79  BEGIN
    80      RETURN x || 'x';
    81  END;
    82  $$
    83      LANGUAGE plpgsql NO SQL;
    84  
    85  
    86  CREATE AGGREGATE agg_prefunc(numeric, numeric) (
    87      SFUNC = mysfunc_accum,
    88      STYPE = numeric,
    89      INITCOND = '0',
    90      PREFUNC = mypre_accum
    91  );
    92  
    93  
    94  CREATE AGGREGATE agg_test(integer) (
    95      SFUNC = int4xor,
    96      STYPE = integer,
    97      INITCOND = '0'
    98  );
    99  
   100  
   101  CREATE OPERATOR #### (
   102      PROCEDURE = numeric_fac,
   103      LEFTARG = bigint
   104  );
   105  
   106  
   107  CREATE OPERATOR CLASS testclass
   108  	FOR TYPE int USING gist AS
   109  	OPERATOR 1 = RECHECK,
   110  	OPERATOR 2 < ,
   111  	FUNCTION 1 abs(integer),
   112  	FUNCTION 2 int4out(integer);
   113  
   114  CREATE OPERATOR CLASS range_class
   115  	FOR TYPE text USING btree AS
   116  	STORAGE text;
   117  
   118  SET default_tablespace = '';
   119  
   120  
   121  CREATE TABLE bar (
   122      i integer NOT NULL,
   123      j text,
   124      k smallint NOT NULL,
   125      l character varying(20)
   126  ) DISTRIBUTED BY (i);
   127  
   128  
   129  
   130  
   131  COPY bar (i, j, k, l) FROM stdin;
   132  \.
   133  
   134  
   135  CREATE TABLE foo (
   136      k text,
   137      i integer,
   138      j text
   139  ) DISTRIBUTED RANDOMLY;
   140  
   141  
   142  COPY foo (k, i, j) FROM stdin;
   143  \.
   144  
   145  
   146  CREATE TABLE foo2 (
   147      k text,
   148      l character varying(20)
   149  )
   150  INHERITS (foo) DISTRIBUTED RANDOMLY;
   151  
   152  
   153  COPY foo2 (k, i, j, l) FROM stdin;
   154  \.
   155  
   156  
   157  SET search_path = schema2, pg_catalog;
   158  
   159  
   160  CREATE TABLE foo3 (
   161      m double precision
   162  )
   163  INHERITS (public.foo2) DISTRIBUTED RANDOMLY;
   164  
   165  
   166  
   167  SET search_path = public, pg_catalog;
   168  
   169  
   170  CREATE TABLE foo4 (
   171      n integer
   172  )
   173  INHERITS (schema2.foo3) DISTRIBUTED RANDOMLY;
   174  
   175  
   176  COPY foo4 (k, i, j, l, m, n) FROM stdin;
   177  \.
   178  
   179  
   180  
   181  CREATE TABLE gpcrondump_history (
   182      rec_date timestamp without time zone,
   183      start_time character(8),
   184      end_time character(8),
   185      options text,
   186      dump_key character varying(20),
   187      dump_exit_status smallint,
   188      script_exit_status smallint,
   189      exit_text character varying(10)
   190  ) DISTRIBUTED BY (rec_date);
   191  
   192  
   193  COPY gpcrondump_history (rec_date, start_time, end_time, options, dump_key, dump_exit_status, script_exit_status, exit_text) FROM stdin;
   194  \.
   195  
   196  
   197  SET search_path = schema2, pg_catalog;
   198  
   199  
   200  COPY foo3 (k, i, j, l, m) FROM stdin;
   201  \.
   202  
   203  
   204  
   205  CREATE TABLE noatts (
   206  ) DISTRIBUTED RANDOMLY;
   207  
   208  
   209  
   210  
   211  COPY noatts  FROM stdin;
   212  \.
   213  
   214  
   215  SET search_path = public, pg_catalog;
   216  
   217  
   218  CREATE TABLE pk_table (
   219      a integer NOT NULL
   220  ) DISTRIBUTED BY (a);
   221  
   222  
   223  
   224  
   225  COPY pk_table (a) FROM stdin;
   226  \.
   227  
   228  
   229  
   230  CREATE TABLE reference_table (
   231      a integer,
   232      b integer
   233  ) DISTRIBUTED BY (a);
   234  
   235  
   236  
   237  
   238  COPY reference_table (a, b) FROM stdin;
   239  \.
   240  
   241  
   242  SET search_path = schema2, pg_catalog;
   243  
   244  
   245  CREATE TABLE prime (
   246      i integer NOT NULL,
   247      j integer
   248  ) DISTRIBUTED BY (i);
   249  
   250  COPY prime (i, j) FROM stdin;
   251  \.
   252  
   253  
   254  SET search_path = public, pg_catalog;
   255  
   256  CREATE WRITABLE EXTERNAL TABLE my_sales_ext (
   257      id integer,
   258      year integer,
   259      qtr integer,
   260      day integer,
   261      region text
   262  ) LOCATION (
   263      'gpfdist://gpdb_test:8080/sales_2010'
   264  )
   265  FORMAT 'csv' (delimiter E',' null E'' escape E'"' quote E'"')
   266  ENCODING 'UTF8' DISTRIBUTED BY (id);
   267  
   268  
   269  CREATE EXTERNAL TABLE sales_1_prt_yr_1_external_partition__ (
   270      id integer,
   271      year integer,
   272      qtr integer,
   273      day integer,
   274      region text
   275  ) LOCATION (
   276      'gpfdist://gpdb_test:8080/sales_2010'
   277  )
   278  FORMAT 'csv' (delimiter E',' null E'' escape E'"' quote E'"')
   279  ENCODING 'UTF8';
   280  
   281  
   282  SET search_path = public, pg_catalog;
   283  
   284  
   285  CREATE TABLE rule_table1 (
   286      i integer
   287  ) DISTRIBUTED BY (i);
   288  
   289  
   290  
   291  
   292  COPY rule_table1 (i) FROM stdin;
   293  \.
   294  
   295  
   296  SET search_path = public, pg_catalog;
   297  
   298  
   299  CREATE TABLE trigger_table1 (
   300      i integer
   301  ) DISTRIBUTED BY (i);
   302  
   303  
   304  
   305  
   306  COPY trigger_table1 (i) FROM stdin;
   307  \.
   308  
   309  
   310  
   311  CREATE TABLE uniq (
   312      i integer
   313  ) DISTRIBUTED BY (i);
   314  
   315  
   316  COPY uniq (i) FROM stdin;
   317  \.
   318  
   319  
   320  SET search_path = schema2, pg_catalog;
   321  
   322  
   323  CREATE TABLE with_multiple_check (
   324      a integer,
   325      b character varying(40),
   326      CONSTRAINT con1 CHECK (((a > 99) AND ((b)::text <> ''::text)))
   327  ) DISTRIBUTED BY (a);
   328  
   329  
   330  COPY with_multiple_check (a, b) FROM stdin;
   331  \.
   332  
   333  CREATE TABLE many_partitions (id int, year int)
   334  DISTRIBUTED BY (id)
   335  PARTITION BY RANGE (year)
   336  ( START (2000) END (2300) EVERY (1),
   337    DEFAULT PARTITION extra );
   338  
   339  
   340  SET search_path = public, pg_catalog;
   341  
   342  
   343  CREATE CONVERSION testconv FOR 'LATIN1' TO 'MULE_INTERNAL' FROM latin1_to_mic;
   344  
   345  
   346  
   347  CREATE VIEW test_view AS
   348      SELECT pk_table.a FROM pk_table;
   349  
   350  
   351  
   352  
   353  CREATE VIEW view_view AS
   354      SELECT test_view.a FROM test_view;
   355  
   356  
   357  
   358  SET search_path = schema2, pg_catalog;
   359  
   360  
   361  CREATE SEQUENCE seq_one
   362      START WITH 3
   363      INCREMENT BY 1
   364      NO MAXVALUE
   365      NO MINVALUE
   366      CACHE 1;
   367  
   368  
   369  ALTER SEQUENCE seq_one OWNED BY prime.j;
   370  
   371  
   372  SELECT pg_catalog.setval('seq_one', 3, false);
   373  
   374  
   375  SET search_path = public, pg_catalog;
   376  
   377  
   378  ALTER TABLE ONLY pk_table
   379      ADD CONSTRAINT pk_table_pkey PRIMARY KEY (a);
   380  
   381  
   382  
   383  ALTER TABLE ONLY uniq
   384      ADD CONSTRAINT uniq_i_key UNIQUE (i);
   385  
   386  
   387  SET search_path = schema2, pg_catalog;
   388  
   389  
   390  ALTER TABLE ONLY prime
   391      ADD CONSTRAINT prime_pkey PRIMARY KEY (i);
   392  
   393  
   394  SET search_path = public, pg_catalog;
   395  
   396  
   397  CREATE INDEX simple_table_idx1 ON foo4 USING btree (n);
   398  
   399  
   400  
   401  CREATE RULE double_insert AS ON INSERT TO rule_table1 DO INSERT INTO rule_table1 VALUES (1);
   402  
   403  
   404  ALTER TABLE ONLY reference_table
   405      ADD CONSTRAINT reference_table_b_fkey FOREIGN KEY (b) REFERENCES pk_table(a);
   406  
   407  
   408  REVOKE ALL ON SCHEMA public FROM PUBLIC;
   409  GRANT ALL ON SCHEMA public TO PUBLIC;