I see, thanks for clarifying.
I think it fails in PG because of a missing pair of parentheses. SA
generates:
CREATE INDEX doc_idx ON xmltab (CAST(xpath('//@value_dbl', doc)[1] AS TEXT))
but it should be:
CREATE INDEX doc_idx ON xmltab (CAST((xpath('//@value_dbl', doc))[1] AS
TEXT))
Subtle but seems to matter to PG... is there a way to enforce an extra pair
of () ?
On Thursday, December 5, 2013 2:08:37 PM UTC-8, Michael Bayer wrote:
>
> OK well the compile(dialect=…) was just to illustrate the string form, we
> don’t put that in the Index, so that way the expression still provides
> access to the column, which it needs to search for in order to get at the
> table:
>
> xmlTable = Table('xmltable', m, Column('doc', TEXT))
> idx = Index("doc_idx",
> cast((func.xpath('//@bla', xmlTable.c.doc,
> type_=postgresql.ARRAY(String())))[1], TEXT))
>
> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True)
> idx.create(e)
>
> the SQL itself still fails on PG (not familiar with the xpath function)
> but it renders:
>
> CREATE INDEX doc_idx ON xmltable (CAST(xpath('//@bla', doc)[1] AS TEXT))
>
>
>
>
>
> On Dec 5, 2013, at 4:47 PM, Christian Lang
> <[email protected]<javascript:>>
> wrote:
>
> Thanks for the quick reply. Getting closer...
>
> I changed the code to
>
> idx = Index("doc_idx", cast((func.xpath('//@bla', xmlTable.c.doc,
> type_=postgresql.ARRAY(String())))[1].compile(dialect=postgresql.dialect()),
> TEXT))
> idx.create(engine)
>
> and the first line is now OK. But the second line (create) gives this
> error:
>
> File "xmltests.py", line 148, in <module>
> idx.create(engine)
> File "build/bdist.linux-x86_64/egg/sqlalchemy/schema.py", line 2519, in
> create
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line
> 1479, in _run_visitor
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line
> 1122, in _run_visitor
> File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/visitors.py", line
> 122, in traverse_single
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/ddl.py", line 105,
> in visit_index
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 662,
> in execute
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/base.py", line 714,
> in _execute_ddl
> File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/expression.py", line
> 1920, in compile
> File "build/bdist.linux-x86_64/egg/sqlalchemy/schema.py", line 2954, in
> _compiler
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/interfaces.py",
> line 787, in __init__
> File "build/bdist.linux-x86_64/egg/sqlalchemy/engine/interfaces.py",
> line 806, in process
> File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/visitors.py", line 80,
> in _compiler_dispatch
> File
> "build/bdist.linux-x86_64/egg/sqlalchemy/dialects/postgresql/base.py", line
> 1086, in visit_create_index
> File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/compiler.py", line
> 2022, in _verify_index_table
> sqlalchemy.exc.CompileError: Index 'doc_idx' is not associated with any
> table.
>
> Which seems strange since "xmlTable" is referenced in the index functional
> expression and it was defined earlier as:
>
> xmlTable = Table("xmltab", metadata,
> Column("document_id", Integer, primary_key=True),
> Column("doc", XML)
> )
>
> (where XML is a UserDefinedType)
>
> Did this table reference get lost? Or is something missing in my index
> definition?
> The table gets created properly in PostgreSQL with XML column btw.
>
> On Thursday, December 5, 2013 12:55:12 PM UTC-8, Michael Bayer wrote:
>>
>> I think if you give your func.xpath a datatype of pg.ARRAY(of whatever)
>> you’ll get it:
>>
>> func.xpath(…, type_=postgresql.ARRAY)[1]
>>
>>
>> >>> from sqlalchemy import func, String
>> >>> from sqlalchemy.dialects import postgresql
>> >>> print func.xpath('something', 'somethingelse',
>> type_=postgresql.ARRAY(String()))[1].compile(dialect=postgresql.dialect())
>> xpath(%(xpath_1)s, %(xpath_2)s)[%(xpath_3)s]
>> >>>
>>
>>
>>
>> On Dec 5, 2013, at 3:33 PM, Christian Lang <[email protected]> wrote:
>>
>> Hi,
>>
>> I'm trying to create the following index (on PostgreSQL) with SQLAlchemy
>> 0.8:
>>
>> CREATE INDEX doc_idx ON xmltab (CAST((xpath('//@bla', doc))[1] AS TEXT));
>>
>> (where doc is a column of type XML)
>>
>> I got this far:
>>
>> Index("doc_idx", cast((func.xpath('//@bla', xmlTable.c.doc))[1], TEXT))
>>
>> but get the error:
>> Traceback (most recent call last):
>> File "xmltests.py", line 146, in <module>
>> idx = Index("doc_idx", cast((func.xpath('//@bla',
>> xmlTable.c.doc))[1], TEXT))
>> File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/operators.py", line
>> 320, in __getitem__
>> File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/expression.py", line
>> 2311, in operate
>> File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/operators.py", line
>> 320, in __getitem__
>> File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/expression.py", line
>> 1994, in operate
>> File "build/bdist.linux-x86_64/egg/sqlalchemy/sql/expression.py", line
>> 2148, in _unsupported_impl
>> NotImplementedError: Operator 'getitem' is not supported on this
>> expression
>>
>> It seems getitem should be allowed since the xpath expression returns an
>> array of nodes (and it is fine in PostgreSQL).
>> Any idea what I am doing wrong and how to fix it?
>>
>> Thanks,
>> Christian
>>
>>
>> --
>> You received this message because you are subscribed to the Google Groups
>> "sqlalchemy" group.
>> To unsubscribe from this group and stop receiving emails from it, send an
>> email to [email protected].
>> To post to this group, send email to [email protected].
>> Visit this group at http://groups.google.com/group/sqlalchemy.
>> For more options, visit https://groups.google.com/groups/opt_out.
>>
>>
>>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected] <javascript:>.
> To post to this group, send email to [email protected]<javascript:>
> .
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.
