Tom Lane wrote:
Since connectby takes a string parameter (correct?) for the table name,
my advice would be to have it not do quote_ident, but instead expect the
user to include double quotes in the string value if dealing with
mixed-case names. Compare the behavior of nextval() for example:
regression=# select nextval('Foo.Bar');
ERROR: Namespace "foo" does not exist
regression=# select nextval('"Foo"."Bar"');
ERROR: Namespace "Foo" does not exist
regression=# select nextval('"Foo.Bar"');
ERROR: Relation "Foo.Bar" does not exist
OK. Attached patch removes calls within the function to quote_ident, requiring
the user to appropriately quote their own identifiers. I also tweaked the
regression test to deal with "value" becoming a reserved word.
If it's not too late, I'd like this to get into 7.3, but in any case, please
apply to HEAD.
Thanks,
Joe
p.s. There are similar issues in dblink, but they appear a bit more difficult
to address. I'll attempt to get them resloved this weekend, again in hopes to
get them applied before 7.3 is released.
Index: contrib/tablefunc/tablefunc.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/tablefunc.c,v
retrieving revision 1.10
diff -c -r1.10 tablefunc.c
*** contrib/tablefunc/tablefunc.c 3 Oct 2002 17:11:12 -0000 1.10
--- contrib/tablefunc/tablefunc.c 22 Nov 2002 22:04:59 -0000
***************
*** 66,72 ****
MemoryContext
per_query_ctx,
AttInMetadata
*attinmeta,
Tuplestorestate
*tupstore);
- static char *quote_ident_cstr(char *rawstr);
typedef struct
{
--- 66,71 ----
***************
*** 776,787 ****
/* 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),
start_with,
! quote_ident_cstr(key_fld));
/* Retrieve the desired rows */
ret = SPI_exec(sql->data, 0);
--- 775,786 ----
/* Build initial sql statement */
appendStringInfo(sql, "SELECT %s, %s FROM %s WHERE %s = '%s' AND %s IS
NOT NULL",
! key_fld,
! parent_key_fld,
! relname,
! parent_key_fld,
start_with,
! key_fld);
/* Retrieve the desired rows */
ret = SPI_exec(sql->data, 0);
***************
*** 1082,1103 ****
}
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;
}
--- 1081,1084 ----
Index: contrib/tablefunc/expected/tablefunc.out
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/expected/tablefunc.out,v
retrieving revision 1.4
diff -c -r1.4 tablefunc.out
*** contrib/tablefunc/expected/tablefunc.out 18 Oct 2002 18:41:21 -0000
1.4
--- contrib/tablefunc/expected/tablefunc.out 22 Nov 2002 23:14:32 -0000
***************
*** 16,122 ****
--
-- crosstab()
--
! CREATE TABLE ct(id int, rowclass text, rowid text, attribute text, value
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;');
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;');
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;');
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;');
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;');
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;');
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;');
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;');
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;');
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;');
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;');
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;');
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);
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);
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);
rowid | att1 | att2 | att3 | att4
-------+------+------+------+------
test1 | val1 | val2 | val3 | val4
--- 16,122 ----
--
-- crosstab()
--
! 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;');
row_name | category_1 | category_2
----------+------------+------------
test1 | val2 | val3
test2 | val6 | val7
(2 rows)
! 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, 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, 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, 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, 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, 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;');
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;');
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;');
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;');
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;');
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;', 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, 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, 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
Index: contrib/tablefunc/sql/tablefunc.sql
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/tablefunc/sql/tablefunc.sql,v
retrieving revision 1.5
diff -c -r1.5 tablefunc.sql
*** contrib/tablefunc/sql/tablefunc.sql 21 Oct 2002 01:42:14 -0000 1.5
--- contrib/tablefunc/sql/tablefunc.sql 22 Nov 2002 23:13:59 -0000
***************
*** 15,42 ****
--
-- crosstab()
--
! CREATE TABLE ct(id int, rowclass text, rowid text, attribute text, value
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, 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, 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, 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 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);
-- test connectby with text based hierarchy
CREATE TABLE connectby_text(keyid text, parent_keyid text);
--- 15,42 ----
--
-- crosstab()
--
! 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'' 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'' 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;', 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);
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
|
