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

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.