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 > <christia...@gmail.com<javascript:>> > 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 sqlalchemy+...@googlegroups.com <javascript:>. > To post to this group, send email to sqlal...@googlegroups.com<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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.