1--
2-- \crosstabview
3--
4
5CREATE TABLE ctv_data (v, h, c, i, d) AS
6VALUES
7   ('v1','h2','foo', 3, '2015-04-01'::date),
8   ('v2','h1','bar', 3, '2015-01-02'),
9   ('v1','h0','baz', NULL, '2015-07-12'),
10   ('v0','h4','qux', 4, '2015-07-15'),
11   ('v0','h4','dbl', -3, '2014-12-15'),
12   ('v0',NULL,'qux', 5, '2014-07-15'),
13   ('v1','h2','quux',7, '2015-04-04');
14
15-- make plans more stable
16ANALYZE ctv_data;
17
18-- running \crosstabview after query uses query in buffer
19SELECT v, EXTRACT(year FROM d), count(*)
20 FROM ctv_data
21 GROUP BY 1, 2
22 ORDER BY 1, 2;
23-- basic usage with 3 columns
24 \crosstabview
25
26-- ordered months in horizontal header, quoted column name
27SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num,
28 count(*) FROM ctv_data  GROUP BY 1,2,3 ORDER BY 1
29 \crosstabview v "month name" 4 num
30
31-- ordered months in vertical header, ordered years in horizontal header
32SELECT EXTRACT(year FROM d) AS year, to_char(d,'Mon') AS """month"" name",
33  EXTRACT(month FROM d) AS month,
34  format('sum=%s avg=%s', sum(i), avg(i)::numeric(2,1))
35  FROM ctv_data
36  GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d)
37ORDER BY month
38\crosstabview """month"" name" year format year
39
40-- combine contents vertically into the same cell (V/H duplicates)
41SELECT v, h, string_agg(c, E'\n') FROM ctv_data GROUP BY v, h ORDER BY 1,2,3
42 \crosstabview 1 2 3
43
44-- horizontal ASC order from window function
45SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r
46FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
47 \crosstabview v h c r
48
49-- horizontal DESC order from window function
50SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r
51FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
52 \crosstabview v h c r
53
54-- horizontal ASC order from window function, NULLs pushed rightmost
55SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r
56FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
57 \crosstabview v h c r
58
59-- only null, no column name, 2 columns: error
60SELECT null,null \crosstabview
61
62-- only null, no column name, 3 columns: works
63SELECT null,null,null \crosstabview
64
65-- null display
66\pset null '#null#'
67SELECT v,h, string_agg(i::text, E'\n') AS i FROM ctv_data
68GROUP BY v, h ORDER BY h,v
69 \crosstabview v h i
70\pset null ''
71
72-- refer to columns by position
73SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n')
74FROM ctv_data GROUP BY v, h ORDER BY h,v
75 \crosstabview 2 1 4
76
77-- refer to columns by positions and names mixed
78SELECT v,h, string_agg(i::text, E'\n') AS i, string_agg(c, E'\n') AS c
79FROM ctv_data GROUP BY v, h ORDER BY h,v
80 \crosstabview 1 "h" 4
81
82-- refer to columns by quoted names, check downcasing of unquoted name
83SELECT 1 as "22", 2 as b, 3 as "Foo"
84 \crosstabview "22" B "Foo"
85
86-- error: bad column name
87SELECT v,h,c,i FROM ctv_data
88 \crosstabview v h j
89
90-- error: need to quote name
91SELECT 1 as "22", 2 as b, 3 as "Foo"
92 \crosstabview 1 2 Foo
93
94-- error: need to not quote name
95SELECT 1 as "22", 2 as b, 3 as "Foo"
96 \crosstabview 1 "B" "Foo"
97
98-- error: bad column number
99SELECT v,h,i,c FROM ctv_data
100 \crosstabview 2 1 5
101
102-- error: same H and V columns
103SELECT v,h,i,c FROM ctv_data
104 \crosstabview 2 h 4
105
106-- error: too many columns
107SELECT a,a,1 FROM generate_series(1,3000) AS a
108 \crosstabview
109
110-- error: only one column
111SELECT 1 \crosstabview
112
113DROP TABLE ctv_data;
114
115-- check error reporting (bug #14476)
116CREATE TABLE ctv_data (x int, y int, v text);
117
118INSERT INTO ctv_data SELECT 1, x, '*' || x FROM generate_series(1,10) x;
119SELECT * FROM ctv_data \crosstabview
120
121INSERT INTO ctv_data VALUES (1, 10, '*'); -- duplicate data to cause error
122SELECT * FROM ctv_data \crosstabview
123
124DROP TABLE ctv_data;
125