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

2002-11-23 Thread Masaru Sugawara 
On Fri, 22 Nov 2002 15:21:48 -0800
Joe Conway [EMAIL PROTECTED] wrote:

 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.
 

Thank you for your quick job.


Regards,
Masaru Sugawara

-
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', '11', 0, '.')
as t(id int, parent_id int, level int, branch text);

 id  | parent_id | level |   branch   
-+---+---+
  11 |   | 0 | 11
 101 |11 | 1 | 11.101
 110 |11 | 1 | 11.110
 111 |   110 | 2 | 11.110.111
(4 rows)




CREATE SCHEMA MS;
drop table MS.Test;
CREATE TABLE MS.Test (id int4, parent_id int4, t text);
INSERT INTO MS.Test VALUES(22, null, 'aaa');
INSERT INTO MS.Test VALUES(202, 22, 'bbb');
INSERT INTO MS.Test VALUES(220, 22, 'ccc');
INSERT INTO MS.Test VALUES(222, 220, 'ddd');
SELECT *
 FROM connectby('MS.Test', 'id', 'parent_id', '22', 0, '.')
as t(id int, parent_id int, level int, branch text);


 id  | parent_id | level |   branch   
-+---+---+
  22 |   | 0 | 22
 202 |22 | 1 | 22.202
 220 |22 | 1 | 22.220
 222 |   220 | 2 | 22.220.222
(4 rows)






---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

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

2002-11-22 Thread 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

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

2002-11-22 Thread Joe Conway 
Joe Conway wrote:


Is quote_ident_required incorrectly dealing with schemas?



Sorry to reply to myself, but another related question; shouldn't the 
following produce Ms.Test?

test=# select quote_ident('Ms.Test');
 quote_ident
-
 Ms.Test
(1 row)

Joe


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

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

2002-11-22 Thread Tom Lane 
Joe Conway [EMAIL PROTECTED] writes:
 Joe Conway wrote:
 Is quote_ident_required incorrectly dealing with schemas?

 Sorry to reply to myself, but another related question; shouldn't the 
 following produce Ms.Test?

 test=# select quote_ident('Ms.Test');
   quote_ident
 -
   Ms.Test
 (1 row)

No, it should not.  If it did, it would fail to cope with tablenames
containing dots.

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

regards, tom lane

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]