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;