aboutsummaryrefslogtreecommitdiff
path: root/src/test
diff options
context:
space:
mode:
Diffstat (limited to 'src/test')
-rw-r--r--src/test/regress/expected/psql.out189
-rw-r--r--src/test/regress/sql/psql.sql105
2 files changed, 294 insertions, 0 deletions
diff --git a/src/test/regress/expected/psql.out b/src/test/regress/expected/psql.out
index edcc414630c..9e98e9d62fd 100644
--- a/src/test/regress/expected/psql.out
+++ b/src/test/regress/expected/psql.out
@@ -2476,6 +2476,7 @@ execute q;
+------------------+-------------------+
deallocate q;
+\pset linestyle ascii
prepare q as select ' | = | lkjsafi\\/ /oeu rio)(!@&*#)*(!&@*) \ (&' as " | -- | 012345678 9abc def!*@#&!@(*&*~~_+-=\ \", '11' as "0123456789", 11 as int from generate_series(1,10) as n;
\pset format asciidoc
\pset expanded off
@@ -2682,6 +2683,9 @@ execute q;
<l|int >l|11
|====
deallocate q;
+\pset format aligned
+\pset expanded off
+\pset border 1
-- SHOW_CONTEXT
\set SHOW_CONTEXT never
do $$
@@ -2710,3 +2714,188 @@ NOTICE: foo
CONTEXT: PL/pgSQL function inline_code_block line 3 at RAISE
ERROR: bar
CONTEXT: PL/pgSQL function inline_code_block line 4 at RAISE
+--
+-- \crosstabview
+--
+CREATE TABLE ctv_data (v, h, c, i, d) AS
+VALUES
+ ('v1','h2','foo', 3, '2015-04-01'::date),
+ ('v2','h1','bar', 3, '2015-01-02'),
+ ('v1','h0','baz', NULL, '2015-07-12'),
+ ('v0','h4','qux', 4, '2015-07-15'),
+ ('v0','h4','dbl', -3, '2014-12-15'),
+ ('v0',NULL,'qux', 5, '2014-07-15'),
+ ('v1','h2','quux',7, '2015-04-04');
+-- running \crosstabview after query uses query in buffer
+SELECT v, EXTRACT(year FROM d), count(*)
+ FROM ctv_data
+ GROUP BY 1, 2
+ ORDER BY 1, 2;
+ v | date_part | count
+----+-----------+-------
+ v0 | 2014 | 2
+ v0 | 2015 | 1
+ v1 | 2015 | 3
+ v2 | 2015 | 1
+(4 rows)
+
+-- basic usage with 3 columns
+ \crosstabview
+ v | 2014 | 2015
+----+------+------
+ v0 | 2 | 1
+ v1 | | 3
+ v2 | | 1
+(3 rows)
+
+-- ordered months in horizontal header, quoted column name
+SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num,
+ count(*) FROM ctv_data GROUP BY 1,2,3 ORDER BY 1
+ \crosstabview v "month name":num 4
+ v | Jan | Apr | Jul | Dec
+----+-----+-----+-----+-----
+ v0 | | | 2 | 1
+ v1 | | 2 | 1 |
+ v2 | 1 | | |
+(3 rows)
+
+-- ordered months in vertical header, ordered years in horizontal header
+SELECT EXTRACT(year FROM d) AS year, to_char(d,'Mon') AS "month name",
+ EXTRACT(month FROM d) AS month,
+ format('sum=%s avg=%s', sum(i), avg(i)::numeric(2,1))
+ FROM ctv_data
+ GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d)
+ORDER BY month
+\crosstabview "month name" year:year format
+ month name | 2014 | 2015
+------------+-----------------+----------------
+ Jan | | sum=3 avg=3.0
+ Apr | | sum=10 avg=5.0
+ Jul | sum=5 avg=5.0 | sum=4 avg=4.0
+ Dec | sum=-3 avg=-3.0 |
+(4 rows)
+
+-- combine contents vertically into the same cell (V/H duplicates)
+SELECT v, h, string_agg(c, E'\n') FROM ctv_data GROUP BY v, h ORDER BY 1,2,3
+ \crosstabview 1 2 3
+ v | h4 | | h0 | h2 | h1
+----+-----+-----+-----+------+-----
+ v0 | qux+| qux | | |
+ | dbl | | | |
+ v1 | | | baz | foo +|
+ | | | | quux |
+ v2 | | | | | bar
+(3 rows)
+
+-- horizontal ASC order from window function
+SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r
+FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
+ \crosstabview v h:r c
+ v | h0 | h1 | h2 | h4 |
+----+-----+-----+------+-----+-----
+ v0 | | | | qux+| qux
+ | | | | dbl |
+ v1 | baz | | foo +| |
+ | | | quux | |
+ v2 | | bar | | |
+(3 rows)
+
+-- horizontal DESC order from window function
+SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r
+FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
+ \crosstabview v h:r c
+ v | | h4 | h2 | h1 | h0
+----+-----+-----+------+-----+-----
+ v0 | qux | qux+| | |
+ | | dbl | | |
+ v1 | | | foo +| | baz
+ | | | quux | |
+ v2 | | | | bar |
+(3 rows)
+
+-- horizontal ASC order from window function, NULLs pushed rightmost
+SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r
+FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
+ \crosstabview v h:r c
+ v | h0 | h1 | h2 | h4 |
+----+-----+-----+------+-----+-----
+ v0 | | | | qux+| qux
+ | | | | dbl |
+ v1 | baz | | foo +| |
+ | | | quux | |
+ v2 | | bar | | |
+(3 rows)
+
+-- only null, no column name, 2 columns: error
+SELECT null,null \crosstabview
+The query must return at least two columns to be shown in crosstab
+-- only null, no column name, 3 columns: works
+SELECT null,null,null \crosstabview
+ ?column? |
+----------+--
+ |
+(1 row)
+
+-- null display
+\pset null '#null#'
+SELECT v,h, string_agg(i::text, E'\n') AS i FROM ctv_data
+GROUP BY v, h ORDER BY h,v
+ \crosstabview v h i
+ v | h0 | h1 | h2 | h4 | #null#
+----+--------+----+----+----+--------
+ v1 | #null# | | 3 +| |
+ | | | 7 | |
+ v2 | | 3 | | |
+ v0 | | | | 4 +| 5
+ | | | | -3 |
+(3 rows)
+
+\pset null ''
+-- refer to columns by position
+SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n')
+FROM ctv_data GROUP BY v, h ORDER BY h,v
+ \crosstabview 2 1 4
+ h | v1 | v2 | v0
+----+------+-----+-----
+ h0 | baz | |
+ h1 | | bar |
+ h2 | foo +| |
+ | quux | |
+ h4 | | | qux+
+ | | | dbl
+ | | | qux
+(5 rows)
+
+-- refer to columns by positions and names mixed
+SELECT v,h, string_agg(i::text, E'\n') AS i, string_agg(c, E'\n') AS c
+FROM ctv_data GROUP BY v, h ORDER BY h,v
+ \crosstabview 1 "h" 4
+ v | h0 | h1 | h2 | h4 |
+----+-----+-----+------+-----+-----
+ v1 | baz | | foo +| |
+ | | | quux | |
+ v2 | | bar | | |
+ v0 | | | | qux+| qux
+ | | | | dbl |
+(3 rows)
+
+-- error: bad column name
+SELECT v,h,c,i FROM ctv_data
+ \crosstabview v h j
+Invalid column name: j
+-- error: bad column number
+SELECT v,h,i,c FROM ctv_data
+ \crosstabview 2 1 5
+Invalid column number: 5
+-- error: same H and V columns
+SELECT v,h,i,c FROM ctv_data
+ \crosstabview 2 h 4
+The same column cannot be used for both vertical and horizontal headers
+-- error: too many columns
+SELECT a,a,1 FROM generate_series(1,3000) AS a
+ \crosstabview
+Maximum number of columns (1600) exceeded
+-- error: only one column
+SELECT 1 \crosstabview
+The query must return at least two columns to be shown in crosstab
+DROP TABLE ctv_data;
diff --git a/src/test/regress/sql/psql.sql b/src/test/regress/sql/psql.sql
index c5b36649491..2e332afc963 100644
--- a/src/test/regress/sql/psql.sql
+++ b/src/test/regress/sql/psql.sql
@@ -326,6 +326,8 @@ execute q;
deallocate q;
+\pset linestyle ascii
+
prepare q as select ' | = | lkjsafi\\/ /oeu rio)(!@&*#)*(!&@*) \ (&' as " | -- | 012345678 9abc def!*@#&!@(*&*~~_+-=\ \", '11' as "0123456789", 11 as int from generate_series(1,10) as n;
\pset format asciidoc
@@ -351,6 +353,10 @@ execute q;
deallocate q;
+\pset format aligned
+\pset expanded off
+\pset border 1
+
-- SHOW_CONTEXT
\set SHOW_CONTEXT never
@@ -373,3 +379,102 @@ begin
raise notice 'foo';
raise exception 'bar';
end $$;
+
+--
+-- \crosstabview
+--
+
+CREATE TABLE ctv_data (v, h, c, i, d) AS
+VALUES
+ ('v1','h2','foo', 3, '2015-04-01'::date),
+ ('v2','h1','bar', 3, '2015-01-02'),
+ ('v1','h0','baz', NULL, '2015-07-12'),
+ ('v0','h4','qux', 4, '2015-07-15'),
+ ('v0','h4','dbl', -3, '2014-12-15'),
+ ('v0',NULL,'qux', 5, '2014-07-15'),
+ ('v1','h2','quux',7, '2015-04-04');
+
+-- running \crosstabview after query uses query in buffer
+SELECT v, EXTRACT(year FROM d), count(*)
+ FROM ctv_data
+ GROUP BY 1, 2
+ ORDER BY 1, 2;
+-- basic usage with 3 columns
+ \crosstabview
+
+-- ordered months in horizontal header, quoted column name
+SELECT v, to_char(d, 'Mon') AS "month name", EXTRACT(month FROM d) AS num,
+ count(*) FROM ctv_data GROUP BY 1,2,3 ORDER BY 1
+ \crosstabview v "month name":num 4
+
+-- ordered months in vertical header, ordered years in horizontal header
+SELECT EXTRACT(year FROM d) AS year, to_char(d,'Mon') AS "month name",
+ EXTRACT(month FROM d) AS month,
+ format('sum=%s avg=%s', sum(i), avg(i)::numeric(2,1))
+ FROM ctv_data
+ GROUP BY EXTRACT(year FROM d), to_char(d,'Mon'), EXTRACT(month FROM d)
+ORDER BY month
+\crosstabview "month name" year:year format
+
+-- combine contents vertically into the same cell (V/H duplicates)
+SELECT v, h, string_agg(c, E'\n') FROM ctv_data GROUP BY v, h ORDER BY 1,2,3
+ \crosstabview 1 2 3
+
+-- horizontal ASC order from window function
+SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h) AS r
+FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
+ \crosstabview v h:r c
+
+-- horizontal DESC order from window function
+SELECT v, h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h DESC) AS r
+FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
+ \crosstabview v h:r c
+
+-- horizontal ASC order from window function, NULLs pushed rightmost
+SELECT v,h, string_agg(c, E'\n') AS c, row_number() OVER(ORDER BY h NULLS LAST) AS r
+FROM ctv_data GROUP BY v, h ORDER BY 1,3,2
+ \crosstabview v h:r c
+
+-- only null, no column name, 2 columns: error
+SELECT null,null \crosstabview
+
+-- only null, no column name, 3 columns: works
+SELECT null,null,null \crosstabview
+
+-- null display
+\pset null '#null#'
+SELECT v,h, string_agg(i::text, E'\n') AS i FROM ctv_data
+GROUP BY v, h ORDER BY h,v
+ \crosstabview v h i
+\pset null ''
+
+-- refer to columns by position
+SELECT v,h,string_agg(i::text, E'\n'), string_agg(c, E'\n')
+FROM ctv_data GROUP BY v, h ORDER BY h,v
+ \crosstabview 2 1 4
+
+-- refer to columns by positions and names mixed
+SELECT v,h, string_agg(i::text, E'\n') AS i, string_agg(c, E'\n') AS c
+FROM ctv_data GROUP BY v, h ORDER BY h,v
+ \crosstabview 1 "h" 4
+
+-- error: bad column name
+SELECT v,h,c,i FROM ctv_data
+ \crosstabview v h j
+
+-- error: bad column number
+SELECT v,h,i,c FROM ctv_data
+ \crosstabview 2 1 5
+
+-- error: same H and V columns
+SELECT v,h,i,c FROM ctv_data
+ \crosstabview 2 h 4
+
+-- error: too many columns
+SELECT a,a,1 FROM generate_series(1,3000) AS a
+ \crosstabview
+
+-- error: only one column
+SELECT 1 \crosstabview
+
+DROP TABLE ctv_data;
generated by cgit v1.2.3 (git 2.25.1) at 2025-06-21 17:53:30 +0000