2 * No.1-1 CREATE EXTENSION
5 CREATE EXTENSION pg_dbms_stats;
7 DROP EXTENSION pg_dbms_stats;
9 CREATE EXTENSION pg_dbms_stats;
11 -- create no superuser and superuser
12 SET client_min_messages = warning;
13 DROP ROLE IF EXISTS regular_user;
14 CREATE ROLE regular_user LOGIN;
15 SET client_min_messages = fatal;
16 CREATE ROLE postgres SUPERUSER CREATEDB CREATEROLE INHERIT LOGIN;
19 CREATE TABLE pt0(id integer, day date);
20 CREATE INDEX pt0_idx ON pt0(id);
21 CREATE TABLE st0(id integer, name char(5));
22 CREATE INDEX st0_idx ON st0(id);
23 CREATE TABLE st1(val integer, str text);
26 CREATE TABLE s0.st0(id integer, num integer);
27 CREATE INDEX st0_idx ON s0.st0(id);
28 CREATE TABLE s0.st1() INHERITS(s0.st0);
29 CREATE INDEX st1_idx ON s0.st1(id);
30 CREATE TABLE s0.st2(id integer, txt text);
31 CREATE INDEX st2_idx ON s0.st2(id);
33 SELECT st0.id, st0.num, st2.txt
34 FROM s0.st0 st0, s0.st2 st2
35 WHERE st0.id = st2.id;
36 CREATE TYPE s0.sc0 AS (num integer, txt text);
37 CREATE FOREIGN DATA WRAPPER test_wrapper;
38 CREATE SERVER test_server
39 FOREIGN DATA WRAPPER test_wrapper;
40 CREATE FOREIGN TABLE s0.sft0(id integer)
42 CREATE SEQUENCE s0.ss0 START 1;
45 CREATE TABLE s1.st0(id integer, num integer);
48 GRANT USAGE ON SCHEMA s0 TO regular_user;
49 GRANT SELECT ON TABLE s0.st2 TO regular_user;
51 CREATE TYPE complex AS (
56 CREATE FUNCTION inform(VARIADIC arr text[]) RETURNS int AS $$
58 str text := 'arguments are ';
61 FOR count IN SELECT i FROM generate_subscripts($1, 1) g(i) LOOP
65 IF $1[count] IS NULL THEN
66 str := str || '<NULL>';
68 str := str || $1[count];
71 RAISE NOTICE '%', str;
76 CREATE VIEW lockd_io AS
77 SELECT relname, heap_blks_read hbr, heap_blks_hit hbh,
78 idx_blks_read ibr, idx_blks_hit ibh
79 FROM pg_statio_user_tables
80 WHERE schemaname = 'dbms_stats'
81 AND relname LIKE '%stats_locked'
85 INSERT INTO st0 VALUES (1, 'test'), (2, 'test');
86 INSERT INTO st1 SELECT i % 3, i % 3 FROM generate_series(1, 10000) t(i);
87 INSERT INTO s0.st0 VALUES (1, 10), (2, 20);
88 INSERT INTO s0.st1 VALUES (4, 40), (5, 50), (6, 60);
89 INSERT INTO s0.st2 VALUES (1, '1'), (2, 'test'), (3, 'comment');
90 INSERT INTO s1.st0 VALUES (1, 15), (2, 25), (3, 35), (4, 45);
92 CREATE INDEX st1_idx ON st1 (val);
93 CREATE INDEX st1_exp ON st1 (lower(str));