Skip to content

Commit 1ff3f42

Browse files
alvherrealvherre
committed
Move \crosstabview regression tests to a separate file
It cannot run in the same parallel group as misc, because it creates a table which is unpredictably visible in that test. Per buildfarm member crake.
1 parent c09b18f commit 1ff3f42

File tree

7 files changed

+286
-286
lines changed

7 files changed

+286
-286
lines changed

‎doc/src/sgml/ref/psql-ref.sgml

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1009,7 +1009,7 @@ testdb=>
10091009
<replaceable class="parameter">colD</replaceable>
10101010
is the output column to project into the grid. If this is not
10111011
specified and there are exactly three columns in the result set,
1012-
the column that isn't
1012+
the column that isn't
10131013
<replaceable class="parameter">colV</replaceable> nor
10141014
<replaceable class="parameter">colH</replaceable>
10151015
is displayed; if there are more columns, an error is thrown.

‎src/test/regress/expected/psql.out

Lines changed: 0 additions & 185 deletions
Original file line numberDiff line numberDiff line change
@@ -2714,188 +2714,3 @@ NOTICE: foo
27142714
CONTEXT: PL/pgSQL function inline_code_block line 3 at RAISE
27152715
ERROR: bar
27162716
CONTEXT: PL/pgSQL function inline_code_block line 4 at RAISE
2717-
--
2718-
-- \crosstabview
2719-
--
2720-
CREATE TABLE ctv_data (v, h, c, i, d) AS
2721-
VALUES
2722-
('v1','h2','foo', 3, '2015-04-01'::date),
2723-
('v2','h1','bar', 3, '2015-01-02'),
2724-
('v1','h0','baz', NULL, '2015-07-12'),
2725-
('v0','h4','qux', 4, '2015-07-15'),
2726-
('v0','h4','dbl', -3, '2014-12-15'),
2727-
('v0',NULL,'qux', 5, '2014-07-15'),
2728-
('v1','h2','quux',7, '2015-04-04');
2729-
-- running \crosstabview after query uses query in buffer
2730-
SELECT v, EXTRACT(year FROM d), count(*)
2731-
FROM ctv_data
2732-
GROUP BY 1, 2
2733-
ORDER BY 1, 2;
2734-
v | date_part | count
2735-
----+-----------+-------
2736-
v0 | 2014 | 2
2737-
v0 | 2015 | 1
2738-
v1 | 2015 | 3
2739-
v2 | 2015 | 1
2740-
(4 rows)
2741-
2742-
-- basic usage with 3 columns
2743-
\crosstabview
2744-
v | 2014 | 2015
2745-
----+------+------
2746-
v0 | 2 | 1
2747-
v1 | | 3
2748-
v2 | | 1
2749-
(3 rows)
2750-
2751-
-- ordered months in horizontal header, quoted column name
2752-
SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num,
2753-
count(*) FROM ctv_data GROUP BY 1,2,3 ORDER BY 1
2754-
\crosstabview v "month name":num 4
2755-
v | Jan | Apr | Jul | Dec
2756-
----+-----+-----+-----+-----
2757-
v0 | | | 2 | 1
2758-
v1 | | 2 | 1 |
2759-
v2 | 1 | | |
2760-
(3 rows)
2761-
2762-
-- ordered months in vertical header, ordered years in horizontal header
2763-
SELECT EXTRACT(year FROM d) AS year, to_char(d,'Mon') AS "month name",
2764-
EXTRACT(month FROM d) AS month,
2765-
format('sum=%s avg=%s', sum(i), avg(i)::numeric(2,1))
2766-
FROM ctv_data
2767-
GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d)
2768-
ORDER BY month
2769-
\crosstabview "month name" year:year format
2770-
month name | 2014 | 2015
2771-
------------+-----------------+----------------
2772-
Jan | | sum=3 avg=3.0
2773-
Apr | | sum=10 avg=5.0
2774-
Jul | sum=5 avg=5.0 | sum=4 avg=4.0
2775-
Dec | sum=-3 avg=-3.0 |
2776-
(4 rows)
2777-
2778-
-- combine contents vertically into the same cell (V/H duplicates)
2779-
SELECT v, h, string_agg(c, E'\n') FROM ctv_data GROUP BY v, h ORDER BY 1,2,3
2780-
\crosstabview 1 2 3
2781-
v | h4 | | h0 | h2 | h1
2782-
----+-----+-----+-----+------+-----
2783-
v0 | qux+| qux | | |
2784-
| dbl | | | |
2785-
v1 | | | baz | foo +|
2786-
| | | | quux |
2787-
v2 | | | | | bar
2788-
(3 rows)
2789-
2790-
-- horizontal ASC order from window function
2791-
SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r
2792-
FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
2793-
\crosstabview v h:r c
2794-
v | h0 | h1 | h2 | h4 |
2795-
----+-----+-----+------+-----+-----
2796-
v0 | | | | qux+| qux
2797-
| | | | dbl |
2798-
v1 | baz | | foo +| |
2799-
| | | quux | |
2800-
v2 | | bar | | |
2801-
(3 rows)
2802-
2803-
-- horizontal DESC order from window function
2804-
SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r
2805-
FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
2806-
\crosstabview v h:r c
2807-
v | | h4 | h2 | h1 | h0
2808-
----+-----+-----+------+-----+-----
2809-
v0 | qux | qux+| | |
2810-
| | dbl | | |
2811-
v1 | | | foo +| | baz
2812-
| | | quux | |
2813-
v2 | | | | bar |
2814-
(3 rows)
2815-
2816-
-- horizontal ASC order from window function, NULLs pushed rightmost
2817-
SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r
2818-
FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
2819-
\crosstabview v h:r c
2820-
v | h0 | h1 | h2 | h4 |
2821-
----+-----+-----+------+-----+-----
2822-
v0 | | | | qux+| qux
2823-
| | | | dbl |
2824-
v1 | baz | | foo +| |
2825-
| | | quux | |
2826-
v2 | | bar | | |
2827-
(3 rows)
2828-
2829-
-- only null, no column name, 2 columns: error
2830-
SELECT null,null \crosstabview
2831-
The query must return at least two columns to be shown in crosstab
2832-
-- only null, no column name, 3 columns: works
2833-
SELECT null,null,null \crosstabview
2834-
?column? |
2835-
----------+--
2836-
|
2837-
(1 row)
2838-
2839-
-- null display
2840-
\pset null '#null#'
2841-
SELECT v,h, string_agg(i::text, E'\n') AS i FROM ctv_data
2842-
GROUP BY v, h ORDER BY h,v
2843-
\crosstabview v h i
2844-
v | h0 | h1 | h2 | h4 | #null#
2845-
----+--------+----+----+----+--------
2846-
v1 | #null# | | 3 +| |
2847-
| | | 7 | |
2848-
v2 | | 3 | | |
2849-
v0 | | | | 4 +| 5
2850-
| | | | -3 |
2851-
(3 rows)
2852-
2853-
\pset null ''
2854-
-- refer to columns by position
2855-
SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n')
2856-
FROM ctv_data GROUP BY v, h ORDER BY h,v
2857-
\crosstabview 2 1 4
2858-
h | v1 | v2 | v0
2859-
----+------+-----+-----
2860-
h0 | baz | |
2861-
h1 | | bar |
2862-
h2 | foo +| |
2863-
| quux | |
2864-
h4 | | | qux+
2865-
| | | dbl
2866-
| | | qux
2867-
(5 rows)
2868-
2869-
-- refer to columns by positions and names mixed
2870-
SELECT v,h, string_agg(i::text, E'\n') AS i, string_agg(c, E'\n') AS c
2871-
FROM ctv_data GROUP BY v, h ORDER BY h,v
2872-
\crosstabview 1 "h" 4
2873-
v | h0 | h1 | h2 | h4 |
2874-
----+-----+-----+------+-----+-----
2875-
v1 | baz | | foo +| |
2876-
| | | quux | |
2877-
v2 | | bar | | |
2878-
v0 | | | | qux+| qux
2879-
| | | | dbl |
2880-
(3 rows)
2881-
2882-
-- error: bad column name
2883-
SELECT v,h,c,i FROM ctv_data
2884-
\crosstabview v h j
2885-
Invalid column name: j
2886-
-- error: bad column number
2887-
SELECT v,h,i,c FROM ctv_data
2888-
\crosstabview 2 1 5
2889-
Invalid column number: 5
2890-
-- error: same H and V columns
2891-
SELECT v,h,i,c FROM ctv_data
2892-
\crosstabview 2 h 4
2893-
The same column cannot be used for both vertical and horizontal headers
2894-
-- error: too many columns
2895-
SELECT a,a,1 FROM generate_series(1,3000) AS a
2896-
\crosstabview
2897-
Maximum number of columns (1600) exceeded
2898-
-- error: only one column
2899-
SELECT 1 \crosstabview
2900-
The query must return at least two columns to be shown in crosstab
2901-
DROP TABLE ctv_data;

‎src/test/regress/expected/psql_crosstab.out

Lines changed: 185 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,185 @@
1+
--
2+
-- \crosstabview
3+
--
4+
CREATE TABLE ctv_data (v, h, c, i, d) AS
5+
VALUES
6+
('v1','h2','foo', 3, '2015-04-01'::date),
7+
('v2','h1','bar', 3, '2015-01-02'),
8+
('v1','h0','baz', NULL, '2015-07-12'),
9+
('v0','h4','qux', 4, '2015-07-15'),
10+
('v0','h4','dbl', -3, '2014-12-15'),
11+
('v0',NULL,'qux', 5, '2014-07-15'),
12+
('v1','h2','quux',7, '2015-04-04');
13+
-- running \crosstabview after query uses query in buffer
14+
SELECT v, EXTRACT(year FROM d), count(*)
15+
FROM ctv_data
16+
GROUP BY 1, 2
17+
ORDER BY 1, 2;
18+
v | date_part | count
19+
----+-----------+-------
20+
v0 | 2014 | 2
21+
v0 | 2015 | 1
22+
v1 | 2015 | 3
23+
v2 | 2015 | 1
24+
(4 rows)
25+
26+
-- basic usage with 3 columns
27+
\crosstabview
28+
v | 2014 | 2015
29+
----+------+------
30+
v0 | 2 | 1
31+
v1 | | 3
32+
v2 | | 1
33+
(3 rows)
34+
35+
-- ordered months in horizontal header, quoted column name
36+
SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num,
37+
count(*) FROM ctv_data GROUP BY 1,2,3 ORDER BY 1
38+
\crosstabview v "month name":num 4
39+
v | Jan | Apr | Jul | Dec
40+
----+-----+-----+-----+-----
41+
v0 | | | 2 | 1
42+
v1 | | 2 | 1 |
43+
v2 | 1 | | |
44+
(3 rows)
45+
46+
-- ordered months in vertical header, ordered years in horizontal header
47+
SELECT EXTRACT(year FROM d) AS year, to_char(d,'Mon') AS "month name",
48+
EXTRACT(month FROM d) AS month,
49+
format('sum=%s avg=%s', sum(i), avg(i)::numeric(2,1))
50+
FROM ctv_data
51+
GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d)
52+
ORDER BY month
53+
\crosstabview "month name" year:year format
54+
month name | 2014 | 2015
55+
------------+-----------------+----------------
56+
Jan | | sum=3 avg=3.0
57+
Apr | | sum=10 avg=5.0
58+
Jul | sum=5 avg=5.0 | sum=4 avg=4.0
59+
Dec | sum=-3 avg=-3.0 |
60+
(4 rows)
61+
62+
-- combine contents vertically into the same cell (V/H duplicates)
63+
SELECT v, h, string_agg(c, E'\n') FROM ctv_data GROUP BY v, h ORDER BY 1,2,3
64+
\crosstabview 1 2 3
65+
v | h4 | | h0 | h2 | h1
66+
----+-----+-----+-----+------+-----
67+
v0 | qux+| qux | | |
68+
| dbl | | | |
69+
v1 | | | baz | foo +|
70+
| | | | quux |
71+
v2 | | | | | bar
72+
(3 rows)
73+
74+
-- horizontal ASC order from window function
75+
SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r
76+
FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
77+
\crosstabview v h:r c
78+
v | h0 | h1 | h2 | h4 |
79+
----+-----+-----+------+-----+-----
80+
v0 | | | | qux+| qux
81+
| | | | dbl |
82+
v1 | baz | | foo +| |
83+
| | | quux | |
84+
v2 | | bar | | |
85+
(3 rows)
86+
87+
-- horizontal DESC order from window function
88+
SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r
89+
FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
90+
\crosstabview v h:r c
91+
v | | h4 | h2 | h1 | h0
92+
----+-----+-----+------+-----+-----
93+
v0 | qux | qux+| | |
94+
| | dbl | | |
95+
v1 | | | foo +| | baz
96+
| | | quux | |
97+
v2 | | | | bar |
98+
(3 rows)
99+
100+
-- horizontal ASC order from window function, NULLs pushed rightmost
101+
SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r
102+
FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
103+
\crosstabview v h:r c
104+
v | h0 | h1 | h2 | h4 |
105+
----+-----+-----+------+-----+-----
106+
v0 | | | | qux+| qux
107+
| | | | dbl |
108+
v1 | baz | | foo +| |
109+
| | | quux | |
110+
v2 | | bar | | |
111+
(3 rows)
112+
113+
-- only null, no column name, 2 columns: error
114+
SELECT null,null \crosstabview
115+
The query must return at least two columns to be shown in crosstab
116+
-- only null, no column name, 3 columns: works
117+
SELECT null,null,null \crosstabview
118+
?column? |
119+
----------+--
120+
|
121+
(1 row)
122+
123+
-- null display
124+
\pset null '#null#'
125+
SELECT v,h, string_agg(i::text, E'\n') AS i FROM ctv_data
126+
GROUP BY v, h ORDER BY h,v
127+
\crosstabview v h i
128+
v | h0 | h1 | h2 | h4 | #null#
129+
----+--------+----+----+----+--------
130+
v1 | #null# | | 3 +| |
131+
| | | 7 | |
132+
v2 | | 3 | | |
133+
v0 | | | | 4 +| 5
134+
| | | | -3 |
135+
(3 rows)
136+
137+
\pset null ''
138+
-- refer to columns by position
139+
SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n')
140+
FROM ctv_data GROUP BY v, h ORDER BY h,v
141+
\crosstabview 2 1 4
142+
h | v1 | v2 | v0
143+
----+------+-----+-----
144+
h0 | baz | |
145+
h1 | | bar |
146+
h2 | foo +| |
147+
| quux | |
148+
h4 | | | qux+
149+
| | | dbl
150+
| | | qux
151+
(5 rows)
152+
153+
-- refer to columns by positions and names mixed
154+
SELECT v,h, string_agg(i::text, E'\n') AS i, string_agg(c, E'\n') AS c
155+
FROM ctv_data GROUP BY v, h ORDER BY h,v
156+
\crosstabview 1 "h" 4
157+
v | h0 | h1 | h2 | h4 |
158+
----+-----+-----+------+-----+-----
159+
v1 | baz | | foo +| |
160+
| | | quux | |
161+
v2 | | bar | | |
162+
v0 | | | | qux+| qux
163+
| | | | dbl |
164+
(3 rows)
165+
166+
-- error: bad column name
167+
SELECT v,h,c,i FROM ctv_data
168+
\crosstabview v h j
169+
Invalid column name: j
170+
-- error: bad column number
171+
SELECT v,h,i,c FROM ctv_data
172+
\crosstabview 2 1 5
173+
Invalid column number: 5
174+
-- error: same H and V columns
175+
SELECT v,h,i,c FROM ctv_data
176+
\crosstabview 2 h 4
177+
The same column cannot be used for both vertical and horizontal headers
178+
-- error: too many columns
179+
SELECT a,a,1 FROM generate_series(1,3000) AS a
180+
\crosstabview
181+
Maximum number of columns (1600) exceeded
182+
-- error: only one column
183+
SELECT 1 \crosstabview
184+
The query must return at least two columns to be shown in crosstab
185+
DROP TABLE ctv_data;

‎src/test/regress/parallel_schedule

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -92,7 +92,7 @@ test: brin gin gist spgist privileges init_privs security_label collate matview
9292
test: alter_generic alter_operator misc psql async dbsize misc_functions
9393

9494
# rules cannot run concurrently with any test that creates a view
95-
test: rules
95+
test: rules psql_crosstab
9696

9797
# ----------
9898
# Another group of parallel tests

0 commit comments

Comments
 (0)