Postgres Pro
Downloads
Home   >   mailing lists

Thread: connectby with schema

connectby with schema

From
Masaru Sugawara
Date:
Hi, all

While testing RC1, I found CONNECTBY had another problem. 
It seems to me that SCHEMA can't be used in CONNECTBY.
Is it just in time for 7.3 to be added to TODO items ?



CREATE TABLE test (id int4, parent_id int4, t text);
INSERT INTO test VALUES(11, null, 'aaa');
INSERT INTO test VALUES(101, 11, 'bbb');
INSERT INTO test VALUES(110, 11, 'ccc');
INSERT INTO test VALUES(111, 110, 'ddd');
SELECT *FROM connectby('test', 'id', 'parent_id', '11', 0, '.')           as t(id int4, parent_id int4, level int,
branchtext);
 
id  | parent_id | level |   branch   
-----+-----------+-------+------------ 11 |           |     0 | 11101 |        11 |     1 | 11.101110 |        11 |
1| 11.110111 |       110 |     2 | 11.110.111
 
(4 rows)



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,
branchtext);
 

ERROR:  Relation "ms.test" does not exist



Regards,
Masaru Sugawara

quote_ident and schemas (was Re: connectby with schema)

From
Joe Conway
Date:
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

Re: quote_ident and schemas (was Re: connectby with schema)

From
Joe Conway
Date:
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

Re: quote_ident and schemas (was Re: connectby with schema)

From
Stephan Szabo
Date:
On Fri, 22 Nov 2002, Joe Conway wrote:

> 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

I think the query result here is correct behavior since in the second the
period shouldn't be a separator for schema and table but instead be part
of the identifier.

Dropping some bits that probably aren't important and merging some states

<table name> -> <qualified name>
<qualified name> -> [<schema name> <period>] <identifier>
<identifer> -> <regular identifier>  |              <delimited identifier>
<delimited identifier> -> <double quote> <delimited identifier body>          <double quote>

I'd think that they'd parse like:ms.test  -> <identifier> . <identifier>
"ms.test" -> <delimited identifier>

The first would match <schema name> <period> <identifier>, but the second
would not.

Re: quote_ident and schemas (was Re: connectby with schema)

From
Tom Lane
Date:
Joe Conway <mail@joeconway.com> 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

Re: quote_ident and schemas (was Re: connectby with schema)

From
Masaru Sugawara
Date:
On Fri, 22 Nov 2002 15:21:48 -0800
Joe Conway <mail@joeconway.com> 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,
branchtext);
 
id  | parent_id | level |   branch   
-----+-----------+-------+------------ 11 |           |     0 | 11101 |        11 |     1 | 11.101110 |        11 |
1| 11.110111 |       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,
branchtext);
 

id  | parent_id | level |   branch   
-----+-----------+-------+------------ 22 |           |     0 | 22202 |        22 |     1 | 22.202220 |        22 |
1| 22.220222 |       220 |     2 | 22.220.222
 
(4 rows)