From 1f1c332381fdc3927c99478a01d47aea4c5b0f10 Mon Sep 17 00:00:00 2001 From: Tom Lane Date: Sat, 23 Nov 2002 01:54:09 +0000 Subject: [PATCH] Remove inappropriate double-quoting in connectby() code; adjust regression test to avoid using VALUE as a name. From Joe Conway. --- contrib/tablefunc/README.tablefunc | 8 ++++-- contrib/tablefunc/expected/tablefunc.out | 32 ++++++++++++------------ contrib/tablefunc/sql/tablefunc.sql | 32 ++++++++++++------------ contrib/tablefunc/tablefunc.c | 29 ++++----------------- 4 files changed, 43 insertions(+), 58 deletions(-) diff --git a/contrib/tablefunc/README.tablefunc b/contrib/tablefunc/README.tablefunc index c1faf190b3..53d4cb99f2 100644 --- a/contrib/tablefunc/README.tablefunc +++ b/contrib/tablefunc/README.tablefunc @@ -395,8 +395,12 @@ Notes for branch_delim for internal recursion detection, even though the branch field is not returned. - 4. If the branch field is desired, it must be the forth column in the query - column definition, and it must be type TEXT + 4. If the branch field is desired, it must be the fourth column in the query + column definition, and it must be type TEXT. + + 5. The parameters representing table and field names must include double + quotes if the names are mixed-case or contain special characters. + Example usage diff --git a/contrib/tablefunc/expected/tablefunc.out b/contrib/tablefunc/expected/tablefunc.out index 8afcdebdf7..a06355c9c1 100644 --- a/contrib/tablefunc/expected/tablefunc.out +++ b/contrib/tablefunc/expected/tablefunc.out @@ -16,107 +16,107 @@ SELECT avg(normal_rand)::int FROM normal_rand(100, 250, 0.2, EXTRACT(SECONDS FRO -- -- crosstab() -- -CREATE TABLE ct(id int, rowclass text, rowid text, attribute text, value text); +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, value 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 test2 | val6 | val7 (2 rows) -SELECT * FROM crosstab3('SELECT rowid, attribute, value 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 | test2 | val6 | val7 | (2 rows) -SELECT * FROM crosstab4('SELECT rowid, attribute, value 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 | | test2 | val6 | val7 | | (2 rows) -SELECT * FROM crosstab2('SELECT rowid, attribute, value 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 test2 | val5 | val6 (2 rows) -SELECT * FROM crosstab3('SELECT rowid, attribute, value 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 test2 | val5 | val6 | val7 (2 rows) -SELECT * FROM crosstab4('SELECT rowid, attribute, value 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 test2 | val5 | val6 | val7 | val8 (2 rows) -SELECT * FROM crosstab2('SELECT rowid, attribute, value 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, value 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, value 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, value 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, value 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, value 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, value FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 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;', 2) AS c(rowid text, att1 text, att2 text); rowid | att1 | att2 -------+------+------ test1 | val1 | val2 test2 | val5 | val6 (2 rows) -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); +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); rowid | att1 | att2 | att3 -------+------+------+------ test1 | val1 | val2 | val3 test2 | val5 | val6 | val7 (2 rows) -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); +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); rowid | att1 | att2 | att3 | att4 -------+------+------+------+------ test1 | val1 | val2 | val3 | val4 diff --git a/contrib/tablefunc/sql/tablefunc.sql b/contrib/tablefunc/sql/tablefunc.sql index bafed1e5c3..7ca399fd07 100644 --- a/contrib/tablefunc/sql/tablefunc.sql +++ b/contrib/tablefunc/sql/tablefunc.sql @@ -15,28 +15,28 @@ SELECT avg(normal_rand)::int FROM normal_rand(100, 250, 0.2, EXTRACT(SECONDS FRO -- -- crosstab() -- -CREATE TABLE ct(id int, rowclass text, rowid text, attribute text, value text); +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, value FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;'); -SELECT * FROM crosstab3('SELECT rowid, attribute, value FROM ct where rowclass = ''group1'' and (attribute = ''att2'' or attribute = ''att3'') ORDER BY 1,2;'); -SELECT * FROM crosstab4('SELECT rowid, attribute, value 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, value FROM ct where rowclass = ''group1'' ORDER BY 1,2;'); -SELECT * FROM crosstab3('SELECT rowid, attribute, value FROM ct where rowclass = ''group1'' ORDER BY 1,2;'); -SELECT * FROM crosstab4('SELECT rowid, attribute, value 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, value FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;'); -SELECT * FROM crosstab3('SELECT rowid, attribute, value FROM ct where rowclass = ''group2'' and (attribute = ''att1'' or attribute = ''att2'') ORDER BY 1,2;'); -SELECT * FROM crosstab4('SELECT rowid, attribute, value 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, value FROM ct where rowclass = ''group2'' ORDER BY 1,2;'); -SELECT * FROM crosstab3('SELECT rowid, attribute, value FROM ct where rowclass = ''group2'' ORDER BY 1,2;'); -SELECT * FROM crosstab4('SELECT rowid, attribute, value 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, value FROM ct where rowclass = ''group1'' ORDER BY 1,2;', 2) AS c(rowid text, att1 text, att2 text); -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); -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); +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); +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); +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); -- test connectby with text based hierarchy CREATE TABLE connectby_text(keyid text, parent_keyid text); diff --git a/contrib/tablefunc/tablefunc.c b/contrib/tablefunc/tablefunc.c index 6ea1e72b68..d5db3ceed7 100644 --- a/contrib/tablefunc/tablefunc.c +++ b/contrib/tablefunc/tablefunc.c @@ -66,7 +66,6 @@ static Tuplestorestate *build_tuplestore_recursively(char *key_fld, MemoryContext per_query_ctx, AttInMetadata *attinmeta, Tuplestorestate *tupstore); -static char *quote_ident_cstr(char *rawstr); typedef struct { @@ -776,12 +775,12 @@ build_tuplestore_recursively(char *key_fld, /* Build initial sql statement */ appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = '%s' AND %s IS NOT NULL", - quote_ident_cstr(key_fld), - quote_ident_cstr(parent_key_fld), - quote_ident_cstr(relname), - quote_ident_cstr(parent_key_fld), + key_fld, + parent_key_fld, + relname, + parent_key_fld, start_with, - quote_ident_cstr(key_fld)); + key_fld); /* Retrieve the desired rows */ ret = SPI_exec(sql->data, 0); @@ -1083,21 +1082,3 @@ make_crosstab_tupledesc(TupleDesc spi_tupdesc, int num_catagories) return tupdesc; } - -/* - * Return a properly quoted identifier. - * Uses quote_ident in quote.c - */ -static char * -quote_ident_cstr(char *rawstr) -{ - text *rawstr_text; - text *result_text; - char *result; - - rawstr_text = DatumGetTextP(DirectFunctionCall1(textin, CStringGetDatum(rawstr))); - result_text = DatumGetTextP(DirectFunctionCall1(quote_ident, PointerGetDatum(rawstr_text))); - result = DatumGetCString(DirectFunctionCall1(textout, PointerGetDatum(result_text))); - - return result; -} -- 2.40.0