Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit1ff3f42

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

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;
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)
[8]ページ先頭
©2009-2025 Movatter.jp