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 > <christia...@gmail.com<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 <christia...@gmail.com> 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. >> To post to this group, send email to sqlal...@googlegroups.com. >> 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+...@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.