Folks,
While doing some research for the upcoming (UN)PIVOT proposal, I
noticed that there were some hairy and no-longer-needed bits in the
regression tests for tablefunc, which I have shaved off with the
attached patch.
What say?
Cheers,
David.
--
David Fetter <da...@fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fet...@gmail.com
Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate
diff --git a/contrib/tablefunc/expected/tablefunc.out
b/contrib/tablefunc/expected/tablefunc.out
index fffadc6..6a43906 100644
--- a/contrib/tablefunc/expected/tablefunc.out
+++ b/contrib/tablefunc/expected/tablefunc.out
@@ -14,7 +14,7 @@ SELECT avg(normal_rand)::int FROM normal_rand(100, 250, 0.2);
--
CREATE TABLE ct(id int, rowclass text, rowid text, attribute text, val text);
\copy ct from 'data/ct.data'
-SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
+SELECT * FROM crosstab2($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group1' and (attribute = 'att2' or attribute = 'att3') ORDER BY 1,2;$$);
row_name | category_1 | category_2
----------+------------+------------
test1 | val2 | val3
@@ -22,7 +22,7 @@ SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct
where rowclass = '
| val10 | val11
(3 rows)
-SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
+SELECT * FROM crosstab3($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group1' and (attribute = 'att2' or attribute = 'att3') ORDER BY 1,2;$$);
row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
test1 | val2 | val3 |
@@ -30,7 +30,7 @@ SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct
where rowclass = '
| val10 | val11 |
(3 rows)
-SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
+SELECT * FROM crosstab4($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group1' and (attribute = 'att2' or attribute = 'att3') ORDER BY 1,2;$$);
row_name | category_1 | category_2 | category_3 | category_4
----------+------------+------------+------------+------------
test1 | val2 | val3 | |
@@ -38,7 +38,7 @@ SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct
where rowclass = '
| val10 | val11 | |
(3 rows)
-SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' ORDER BY 1,2;');
+SELECT * FROM crosstab2($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group1' ORDER BY 1,2;$$);
row_name | category_1 | category_2
----------+------------+------------
test1 | val1 | val2
@@ -46,7 +46,7 @@ SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct
where rowclass = '
| val9 | val10
(3 rows)
-SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' ORDER BY 1,2;');
+SELECT * FROM crosstab3($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group1' ORDER BY 1,2;$$);
row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
test1 | val1 | val2 | val3
@@ -54,7 +54,7 @@ SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct
where rowclass = '
| val9 | val10 | val11
(3 rows)
-SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' ORDER BY 1,2;');
+SELECT * FROM crosstab4($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group1' ORDER BY 1,2;$$);
row_name | category_1 | category_2 | category_3 | category_4
----------+------------+------------+------------+------------
test1 | val1 | val2 | val3 | val4
@@ -62,49 +62,49 @@ SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM
ct where rowclass = '
| val9 | val10 | val11 | val12
(3 rows)
-SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass =
''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
+SELECT * FROM crosstab2($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group2' and (attribute = 'att1' or attribute = 'att2') ORDER BY 1,2;$$);
row_name | category_1 | category_2
----------+------------+------------
test3 | val1 | val2
test4 | val4 | val5
(2 rows)
-SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass =
''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
+SELECT * FROM crosstab3($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group2' and (attribute = 'att1' or attribute = 'att2') ORDER BY 1,2;$$);
row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
test3 | val1 | val2 |
test4 | val4 | val5 |
(2 rows)
-SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass =
''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
+SELECT * FROM crosstab4($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group2' and (attribute = 'att1' or attribute = 'att2') ORDER BY 1,2;$$);
row_name | category_1 | category_2 | category_3 | category_4
----------+------------+------------+------------+------------
test3 | val1 | val2 | |
test4 | val4 | val5 | |
(2 rows)
-SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass =
''group2'' ORDER BY 1,2;');
+SELECT * FROM crosstab2($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group2' ORDER BY 1,2;$$);
row_name | category_1 | category_2
----------+------------+------------
test3 | val1 | val2
test4 | val4 | val5
(2 rows)
-SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass =
''group2'' ORDER BY 1,2;');
+SELECT * FROM crosstab3($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group2' ORDER BY 1,2;$$);
row_name | category_1 | category_2 | category_3
----------+------------+------------+------------
test3 | val1 | val2 | val3
test4 | val4 | val5 | val6
(2 rows)
-SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass =
''group2'' ORDER BY 1,2;');
+SELECT * FROM crosstab4($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group2' ORDER BY 1,2;$$);
row_name | category_1 | category_2 | category_3 | category_4
----------+------------+------------+------------+------------
test3 | val1 | val2 | val3 |
test4 | val4 | val5 | val6 |
(2 rows)
-SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text);
+SELECT * FROM crosstab($$SELECT rowid, attribute, val FROM ct where rowclass =
'group1' ORDER BY 1,2;$$) AS c(rowid text, att1 text, att2 text);
rowid | att1 | att2
-------+------+-------
test1 | val1 | val2
@@ -112,7 +112,7 @@ SELECT * FROM crosstab('SELECT rowid, attribute, val FROM
ct where rowclass = ''
| val9 | val10
(3 rows)
-SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text, att3 text);
+SELECT * FROM crosstab($$SELECT rowid, attribute, val FROM ct where rowclass =
'group1' ORDER BY 1,2;$$) AS c(rowid text, att1 text, att2 text, att3 text);
rowid | att1 | att2 | att3
-------+------+-------+-------
test1 | val1 | val2 | val3
@@ -120,7 +120,7 @@ SELECT * FROM crosstab('SELECT rowid, attribute, val FROM
ct where rowclass = ''
| val9 | val10 | val11
(3 rows)
-SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text, att3 text,
att4 text);
+SELECT * FROM crosstab($$SELECT rowid, attribute, val FROM ct where rowclass =
'group1' ORDER BY 1,2;$$) AS c(rowid text, att1 text, att2 text, att3 text,
att4 text);
rowid | att1 | att2 | att3 | att4
-------+------+-------+-------+-------
test1 | val1 | val2 | val3 | val4
@@ -134,7 +134,7 @@ CREATE FUNCTION crosstab_out(text,
RETURNS setof record
AS '$libdir/tablefunc','crosstab'
LANGUAGE C STABLE STRICT;
-SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where
rowclass = ''group1'' ORDER BY 1,2;');
+SELECT * FROM crosstab_out($$SELECT rowid, attribute, val FROM ct where
rowclass = 'group1' ORDER BY 1,2;$$);
rowid | att1 | att2 | att3
-------+------+-------+-------
test1 | val1 | val2 | val3
@@ -199,7 +199,7 @@ AS c(rowid text, rowdt timestamp, temperature int4,
test_result text, test_start
-- source query and category query out of sync
SELECT * FROM crosstab(
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
- 'SELECT DISTINCT attribute FROM cth WHERE attribute IN
(''temperature'',''test_result'',''test_startdate'') ORDER BY 1')
+ $$SELECT DISTINCT attribute FROM cth WHERE attribute IN
('temperature','test_result','test_startdate') ORDER BY 1$$)
AS c(rowid text, rowdt timestamp, temperature int4, test_result text,
test_startdate timestamp);
rowid | rowdt | temperature | test_result |
test_startdate
-------+--------------------------+-------------+-------------+--------------------------
@@ -211,7 +211,7 @@ AS c(rowid text, rowdt timestamp, temperature int4,
test_result text, test_start
-- if category query generates no rows, get expected error
SELECT * FROM crosstab(
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
- 'SELECT DISTINCT attribute FROM cth WHERE attribute = ''a'' ORDER BY 1')
+ $$SELECT DISTINCT attribute FROM cth WHERE attribute = 'a' ORDER BY 1$$)
AS c(rowid text, rowdt timestamp, temperature int4, test_result text,
test_startdate timestamp, volts float8);
ERROR: provided "categories" SQL must return 1 column of at least one row
-- if category query generates more than one column, get expected error
diff --git a/contrib/tablefunc/sql/tablefunc.sql
b/contrib/tablefunc/sql/tablefunc.sql
index ec375b0..f7d340b 100644
--- a/contrib/tablefunc/sql/tablefunc.sql
+++ b/contrib/tablefunc/sql/tablefunc.sql
@@ -12,25 +12,25 @@ SELECT avg(normal_rand)::int FROM normal_rand(100, 250,
0.2);
CREATE TABLE ct(id int, rowclass text, rowid text, attribute text, val text);
\copy ct from 'data/ct.data'
-SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
-SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
-SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
+SELECT * FROM crosstab2($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group1' and (attribute = 'att2' or attribute = 'att3') ORDER BY 1,2;$$);
+SELECT * FROM crosstab3($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group1' and (attribute = 'att2' or attribute = 'att3') ORDER BY 1,2;$$);
+SELECT * FROM crosstab4($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group1' and (attribute = 'att2' or attribute = 'att3') ORDER BY 1,2;$$);
-SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' ORDER BY 1,2;');
-SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' ORDER BY 1,2;');
-SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' ORDER BY 1,2;');
+SELECT * FROM crosstab2($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group1' ORDER BY 1,2;$$);
+SELECT * FROM crosstab3($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group1' ORDER BY 1,2;$$);
+SELECT * FROM crosstab4($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group1' ORDER BY 1,2;$$);
-SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass =
''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
-SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass =
''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
-SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass =
''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
+SELECT * FROM crosstab2($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group2' and (attribute = 'att1' or attribute = 'att2') ORDER BY 1,2;$$);
+SELECT * FROM crosstab3($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group2' and (attribute = 'att1' or attribute = 'att2') ORDER BY 1,2;$$);
+SELECT * FROM crosstab4($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group2' and (attribute = 'att1' or attribute = 'att2') ORDER BY 1,2;$$);
-SELECT * FROM crosstab2('SELECT rowid, attribute, val FROM ct where rowclass =
''group2'' ORDER BY 1,2;');
-SELECT * FROM crosstab3('SELECT rowid, attribute, val FROM ct where rowclass =
''group2'' ORDER BY 1,2;');
-SELECT * FROM crosstab4('SELECT rowid, attribute, val FROM ct where rowclass =
''group2'' ORDER BY 1,2;');
+SELECT * FROM crosstab2($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group2' ORDER BY 1,2;$$);
+SELECT * FROM crosstab3($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group2' ORDER BY 1,2;$$);
+SELECT * FROM crosstab4($$SELECT rowid, attribute, val FROM ct where rowclass
= 'group2' ORDER BY 1,2;$$);
-SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text);
-SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text, att3 text);
-SELECT * FROM crosstab('SELECT rowid, attribute, val FROM ct where rowclass =
''group1'' ORDER BY 1,2;') AS c(rowid text, att1 text, att2 text, att3 text,
att4 text);
+SELECT * FROM crosstab($$SELECT rowid, attribute, val FROM ct where rowclass =
'group1' ORDER BY 1,2;$$) AS c(rowid text, att1 text, att2 text);
+SELECT * FROM crosstab($$SELECT rowid, attribute, val FROM ct where rowclass =
'group1' ORDER BY 1,2;$$) AS c(rowid text, att1 text, att2 text, att3 text);
+SELECT * FROM crosstab($$SELECT rowid, attribute, val FROM ct where rowclass =
'group1' ORDER BY 1,2;$$) AS c(rowid text, att1 text, att2 text, att3 text,
att4 text);
-- check it works with OUT parameters, too
@@ -40,7 +40,7 @@ RETURNS setof record
AS '$libdir/tablefunc','crosstab'
LANGUAGE C STABLE STRICT;
-SELECT * FROM crosstab_out('SELECT rowid, attribute, val FROM ct where
rowclass = ''group1'' ORDER BY 1,2;');
+SELECT * FROM crosstab_out($$SELECT rowid, attribute, val FROM ct where
rowclass = 'group1' ORDER BY 1,2;$$);
--
-- hash based crosstab
@@ -82,13 +82,13 @@ AS c(rowid text, rowdt timestamp, temperature int4,
test_result text, test_start
-- source query and category query out of sync
SELECT * FROM crosstab(
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
- 'SELECT DISTINCT attribute FROM cth WHERE attribute IN
(''temperature'',''test_result'',''test_startdate'') ORDER BY 1')
+ $$SELECT DISTINCT attribute FROM cth WHERE attribute IN
('temperature','test_result','test_startdate') ORDER BY 1$$)
AS c(rowid text, rowdt timestamp, temperature int4, test_result text,
test_startdate timestamp);
-- if category query generates no rows, get expected error
SELECT * FROM crosstab(
'SELECT rowid, rowdt, attribute, val FROM cth ORDER BY 1',
- 'SELECT DISTINCT attribute FROM cth WHERE attribute = ''a'' ORDER BY 1')
+ $$SELECT DISTINCT attribute FROM cth WHERE attribute = 'a' ORDER BY 1$$)
AS c(rowid text, rowdt timestamp, temperature int4, test_result text,
test_startdate timestamp, volts float8);
-- if category query generates more than one column, get expected error
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
