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

Commit1f1c332

Browse files
committed
Remove inappropriate double-quoting in connectby() code; adjust
regression test to avoid using VALUE as a name. From Joe Conway.
1 parente760d22 commit1f1c332

File tree

4 files changed

+43
-58
lines changed

4 files changed

+43
-58
lines changed

‎contrib/tablefunc/README.tablefunc

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -395,8 +395,12 @@ Notes
395395
for branch_delim for internal recursion detection, even though the branch
396396
field is not returned.
397397

398-
4. If the branch field is desired, it must be the forth column in the query
399-
column definition, and it must be type TEXT
398+
4. If the branch field is desired, it must be the fourth column in the query
399+
column definition, and it must be type TEXT.
400+
401+
5. The parameters representing table and field names must include double
402+
quotes if the names are mixed-case or contain special characters.
403+
400404

401405
Example usage
402406

‎contrib/tablefunc/expected/tablefunc.out

Lines changed: 16 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -16,107 +16,107 @@ SELECT avg(normal_rand)::int FROM normal_rand(100, 250, 0.2, EXTRACT(SECONDS FRO
1616
--
1717
-- crosstab()
1818
--
19-
CREATE TABLE ct(id int, rowclass text, rowid text, attribute text,value text);
19+
CREATE TABLE ct(id int, rowclass text, rowid text, attribute text,val text);
2020
\copy ct from 'data/ct.data'
21-
SELECT * FROM crosstab2('SELECT rowid, attribute,value FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
21+
SELECT * FROM crosstab2('SELECT rowid, attribute,val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
2222
row_name | category_1 | category_2
2323
----------+------------+------------
2424
test1 | val2 | val3
2525
test2 | val6 | val7
2626
(2 rows)
2727

28-
SELECT * FROM crosstab3('SELECT rowid, attribute,value FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
28+
SELECT * FROM crosstab3('SELECT rowid, attribute,val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
2929
row_name | category_1 | category_2 | category_3
3030
----------+------------+------------+------------
3131
test1 | val2 | val3 |
3232
test2 | val6 | val7 |
3333
(2 rows)
3434

35-
SELECT * FROM crosstab4('SELECT rowid, attribute,value FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
35+
SELECT * FROM crosstab4('SELECT rowid, attribute,val FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;');
3636
row_name | category_1 | category_2 | category_3 | category_4
3737
----------+------------+------------+------------+------------
3838
test1 | val2 | val3 | |
3939
test2 | val6 | val7 | |
4040
(2 rows)
4141

42-
SELECT * FROM crosstab2('SELECT rowid, attribute,value FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
42+
SELECT * FROM crosstab2('SELECT rowid, attribute,val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
4343
row_name | category_1 | category_2
4444
----------+------------+------------
4545
test1 | val1 | val2
4646
test2 | val5 | val6
4747
(2 rows)
4848

49-
SELECT * FROM crosstab3('SELECT rowid, attribute,value FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
49+
SELECT * FROM crosstab3('SELECT rowid, attribute,val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
5050
row_name | category_1 | category_2 | category_3
5151
----------+------------+------------+------------
5252
test1 | val1 | val2 | val3
5353
test2 | val5 | val6 | val7
5454
(2 rows)
5555

56-
SELECT * FROM crosstab4('SELECT rowid, attribute,value FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
56+
SELECT * FROM crosstab4('SELECT rowid, attribute,val FROM ct where rowclass = ''group1'' ORDER BY 1,2;');
5757
row_name | category_1 | category_2 | category_3 | category_4
5858
----------+------------+------------+------------+------------
5959
test1 | val1 | val2 | val3 | val4
6060
test2 | val5 | val6 | val7 | val8
6161
(2 rows)
6262

63-
SELECT * FROM crosstab2('SELECT rowid, attribute,value FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
63+
SELECT * FROM crosstab2('SELECT rowid, attribute,val FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
6464
row_name | category_1 | category_2
6565
----------+------------+------------
6666
test3 | val1 | val2
6767
test4 | val4 | val5
6868
(2 rows)
6969

70-
SELECT * FROM crosstab3('SELECT rowid, attribute,value FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
70+
SELECT * FROM crosstab3('SELECT rowid, attribute,val FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
7171
row_name | category_1 | category_2 | category_3
7272
----------+------------+------------+------------
7373
test3 | val1 | val2 |
7474
test4 | val4 | val5 |
7575
(2 rows)
7676

77-
SELECT * FROM crosstab4('SELECT rowid, attribute,value FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
77+
SELECT * FROM crosstab4('SELECT rowid, attribute,val FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;');
7878
row_name | category_1 | category_2 | category_3 | category_4
7979
----------+------------+------------+------------+------------
8080
test3 | val1 | val2 | |
8181
test4 | val4 | val5 | |
8282
(2 rows)
8383

84-
SELECT * FROM crosstab2('SELECT rowid, attribute,value FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
84+
SELECT * FROM crosstab2('SELECT rowid, attribute,val FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
8585
row_name | category_1 | category_2
8686
----------+------------+------------
8787
test3 | val1 | val2
8888
test4 | val4 | val5
8989
(2 rows)
9090

91-
SELECT * FROM crosstab3('SELECT rowid, attribute,value FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
91+
SELECT * FROM crosstab3('SELECT rowid, attribute,val FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
9292
row_name | category_1 | category_2 | category_3
9393
----------+------------+------------+------------
9494
test3 | val1 | val2 | val3
9595
test4 | val4 | val5 | val6
9696
(2 rows)
9797

98-
SELECT * FROM crosstab4('SELECT rowid, attribute,value FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
98+
SELECT * FROM crosstab4('SELECT rowid, attribute,val FROM ct where rowclass = ''group2'' ORDER BY 1,2;');
9999
row_name | category_1 | category_2 | category_3 | category_4
100100
----------+------------+------------+------------+------------
101101
test3 | val1 | val2 | val3 |
102102
test4 | val4 | val5 | val6 |
103103
(2 rows)
104104

105-
SELECT * FROM crosstab('SELECT rowid, attribute,value FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 2) AS c(rowid text, att1 text, att2 text);
105+
SELECT * FROM crosstab('SELECT rowid, attribute,val FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 2) AS c(rowid text, att1 text, att2 text);
106106
rowid | att1 | att2
107107
-------+------+------
108108
test1 | val1 | val2
109109
test2 | val5 | val6
110110
(2 rows)
111111

112-
SELECT * FROM crosstab('SELECT rowid, attribute,value FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 3) AS c(rowid text, att1 text, att2 text, att3 text);
112+
SELECT * FROM crosstab('SELECT rowid, attribute,val FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 3) AS c(rowid text, att1 text, att2 text, att3 text);
113113
rowid | att1 | att2 | att3
114114
-------+------+------+------
115115
test1 | val1 | val2 | val3
116116
test2 | val5 | val6 | val7
117117
(2 rows)
118118

119-
SELECT * FROM crosstab('SELECT rowid, attribute,value FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 4) AS c(rowid text, att1 text, att2 text, att3 text, att4 text);
119+
SELECT * FROM crosstab('SELECT rowid, attribute,val FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 4) AS c(rowid text, att1 text, att2 text, att3 text, att4 text);
120120
rowid | att1 | att2 | att3 | att4
121121
-------+------+------+------+------
122122
test1 | val1 | val2 | val3 | val4

‎contrib/tablefunc/sql/tablefunc.sql

Lines changed: 16 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -15,28 +15,28 @@ SELECT avg(normal_rand)::int FROM normal_rand(100, 250, 0.2, EXTRACT(SECONDS FRO
1515
--
1616
-- crosstab()
1717
--
18-
CREATETABLEct(idint, rowclasstext, rowidtext, attributetext,valuetext);
18+
CREATETABLEct(idint, rowclasstext, rowidtext, attributetext,valtext);
1919
\copy ctfrom'data/ct.data'
2020

21-
SELECT*FROM crosstab2('SELECT rowid, attribute,value FROM ct where rowclass =''group1'' and (attribute =''att2'' or attribute =''att3'') ORDER BY 1,2;');
22-
SELECT*FROM crosstab3('SELECT rowid, attribute,value FROM ct where rowclass =''group1'' and (attribute =''att2'' or attribute =''att3'') ORDER BY 1,2;');
23-
SELECT*FROM crosstab4('SELECT rowid, attribute,value FROM ct where rowclass =''group1'' and (attribute =''att2'' or attribute =''att3'') ORDER BY 1,2;');
21+
SELECT*FROM crosstab2('SELECT rowid, attribute,val FROM ct where rowclass =''group1'' and (attribute =''att2'' or attribute =''att3'') ORDER BY 1,2;');
22+
SELECT*FROM crosstab3('SELECT rowid, attribute,val FROM ct where rowclass =''group1'' and (attribute =''att2'' or attribute =''att3'') ORDER BY 1,2;');
23+
SELECT*FROM crosstab4('SELECT rowid, attribute,val FROM ct where rowclass =''group1'' and (attribute =''att2'' or attribute =''att3'') ORDER BY 1,2;');
2424

25-
SELECT*FROM crosstab2('SELECT rowid, attribute,value FROM ct where rowclass =''group1'' ORDER BY 1,2;');
26-
SELECT*FROM crosstab3('SELECT rowid, attribute,value FROM ct where rowclass =''group1'' ORDER BY 1,2;');
27-
SELECT*FROM crosstab4('SELECT rowid, attribute,value FROM ct where rowclass =''group1'' ORDER BY 1,2;');
25+
SELECT*FROM crosstab2('SELECT rowid, attribute,val FROM ct where rowclass =''group1'' ORDER BY 1,2;');
26+
SELECT*FROM crosstab3('SELECT rowid, attribute,val FROM ct where rowclass =''group1'' ORDER BY 1,2;');
27+
SELECT*FROM crosstab4('SELECT rowid, attribute,val FROM ct where rowclass =''group1'' ORDER BY 1,2;');
2828

29-
SELECT*FROM crosstab2('SELECT rowid, attribute,value FROM ct where rowclass =''group2'' and (attribute =''att1'' or attribute =''att2'') ORDER BY 1,2;');
30-
SELECT*FROM crosstab3('SELECT rowid, attribute,value FROM ct where rowclass =''group2'' and (attribute =''att1'' or attribute =''att2'') ORDER BY 1,2;');
31-
SELECT*FROM crosstab4('SELECT rowid, attribute,value FROM ct where rowclass =''group2'' and (attribute =''att1'' or attribute =''att2'') ORDER BY 1,2;');
29+
SELECT*FROM crosstab2('SELECT rowid, attribute,val FROM ct where rowclass =''group2'' and (attribute =''att1'' or attribute =''att2'') ORDER BY 1,2;');
30+
SELECT*FROM crosstab3('SELECT rowid, attribute,val FROM ct where rowclass =''group2'' and (attribute =''att1'' or attribute =''att2'') ORDER BY 1,2;');
31+
SELECT*FROM crosstab4('SELECT rowid, attribute,val FROM ct where rowclass =''group2'' and (attribute =''att1'' or attribute =''att2'') ORDER BY 1,2;');
3232

33-
SELECT*FROM crosstab2('SELECT rowid, attribute,value FROM ct where rowclass =''group2'' ORDER BY 1,2;');
34-
SELECT*FROM crosstab3('SELECT rowid, attribute,value FROM ct where rowclass =''group2'' ORDER BY 1,2;');
35-
SELECT*FROM crosstab4('SELECT rowid, attribute,value FROM ct where rowclass =''group2'' ORDER BY 1,2;');
33+
SELECT*FROM crosstab2('SELECT rowid, attribute,val FROM ct where rowclass =''group2'' ORDER BY 1,2;');
34+
SELECT*FROM crosstab3('SELECT rowid, attribute,val FROM ct where rowclass =''group2'' ORDER BY 1,2;');
35+
SELECT*FROM crosstab4('SELECT rowid, attribute,val FROM ct where rowclass =''group2'' ORDER BY 1,2;');
3636

37-
SELECT*FROM crosstab('SELECT rowid, attribute,value FROM ct where rowclass =''group1'' ORDER BY 1,2;',2)AS c(rowidtext, att1text, att2text);
38-
SELECT*FROM crosstab('SELECT rowid, attribute,value FROM ct where rowclass =''group1'' ORDER BY 1,2;',3)AS c(rowidtext, att1text, att2text, att3text);
39-
SELECT*FROM crosstab('SELECT rowid, attribute,value FROM ct where rowclass =''group1'' ORDER BY 1,2;',4)AS c(rowidtext, att1text, att2text, att3text, att4text);
37+
SELECT*FROM crosstab('SELECT rowid, attribute,val FROM ct where rowclass =''group1'' ORDER BY 1,2;',2)AS c(rowidtext, att1text, att2text);
38+
SELECT*FROM crosstab('SELECT rowid, attribute,val FROM ct where rowclass =''group1'' ORDER BY 1,2;',3)AS c(rowidtext, att1text, att2text, att3text);
39+
SELECT*FROM crosstab('SELECT rowid, attribute,val FROM ct where rowclass =''group1'' ORDER BY 1,2;',4)AS c(rowidtext, att1text, att2text, att3text, att4text);
4040

4141
-- test connectby with text based hierarchy
4242
CREATETABLEconnectby_text(keyidtext, parent_keyidtext);

‎contrib/tablefunc/tablefunc.c

Lines changed: 5 additions & 24 deletions
Original file line numberDiff line numberDiff line change
@@ -66,7 +66,6 @@ static Tuplestorestate *build_tuplestore_recursively(char *key_fld,
6666
MemoryContextper_query_ctx,
6767
AttInMetadata*attinmeta,
6868
Tuplestorestate*tupstore);
69-
staticchar*quote_ident_cstr(char*rawstr);
7069

7170
typedefstruct
7271
{
@@ -776,12 +775,12 @@ build_tuplestore_recursively(char *key_fld,
776775

777776
/* Build initial sql statement */
778777
appendStringInfo(sql,"SELECT %s, %s FROM %s WHERE %s = '%s' AND %s IS NOT NULL",
779-
quote_ident_cstr(key_fld),
780-
quote_ident_cstr(parent_key_fld),
781-
quote_ident_cstr(relname),
782-
quote_ident_cstr(parent_key_fld),
778+
key_fld,
779+
parent_key_fld,
780+
relname,
781+
parent_key_fld,
783782
start_with,
784-
quote_ident_cstr(key_fld));
783+
key_fld);
785784

786785
/* Retrieve the desired rows */
787786
ret=SPI_exec(sql->data,0);
@@ -1083,21 +1082,3 @@ make_crosstab_tupledesc(TupleDesc spi_tupdesc, int num_catagories)
10831082

10841083
returntupdesc;
10851084
}
1086-
1087-
/*
1088-
* Return a properly quoted identifier.
1089-
* Uses quote_ident in quote.c
1090-
*/
1091-
staticchar*
1092-
quote_ident_cstr(char*rawstr)
1093-
{
1094-
text*rawstr_text;
1095-
text*result_text;
1096-
char*result;
1097-
1098-
rawstr_text=DatumGetTextP(DirectFunctionCall1(textin,CStringGetDatum(rawstr)));
1099-
result_text=DatumGetTextP(DirectFunctionCall1(quote_ident,PointerGetDatum(rawstr_text)));
1100-
result=DatumGetCString(DirectFunctionCall1(textout,PointerGetDatum(result_text)));
1101-
1102-
returnresult;
1103-
}

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp