quote_ident and schemas (was Re: [HACKERS] connectby with schema)

[Joe Conway]

Masaru Sugawara wrote:

CREATE SCHEMA ms;
CREATE TABLE ms.test (id int4, parent_id int4, t text);
INSERT INTO ms.test VALUES(11, null, 'aaa');
INSERT INTO ms.test VALUES(101, 11, 'bbb');
INSERT INTO ms.test VALUES(110, 11, 'ccc');
INSERT INTO ms.test VALUES(111, 110, 'ddd');
SELECT *
 FROM connectby('ms.test', 'id', 'parent_id', '101', 0, '.')
            as t(id int4, parent_id int4, level int, branch text);

ERROR:  Relation "ms.test" does not exist

I've tracked this down to the fact that connectby does a quote_ident on the provided relname, and in quote_ident, (quote_ident_required(t)) ends up being true. The problem will occur even with a simple query:

test=# SELECT id, parent_id FROM ms.test WHERE parent_id = '101' AND id IS NOT NULL;
id | parent_id
----+-----------
(0 rows)
test=# SELECT id, parent_id FROM "ms.test" WHERE parent_id = '101' AND id IS NOT NULL;
ERROR: Relation "ms.test" does not exist

But this is not the behavior for unqualified table names:

test=# select * from foo;
f1
----
1
(1 row)
test=# select * from "foo";
f1
----
1
(1 row)

Is quote_ident_required incorrectly dealing with schemas?

Thanks,

Joe


---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster