Wonderful, that did it! Thanks so much. On Thursday, December 5, 2013 3:01:04 PM UTC-8, Michael Bayer wrote: > > parens can be forced using Grouping > > from sqlalchemy.sql.expression import Grouping > > idx = Index("doc_idx", > cast( > Grouping(func.xpath( > '//@bla', > xmlTable.c.doc, > type_=postgresql.ARRAY(String()) > ))[1], > TEXT) > ) > > > > On Dec 5, 2013, at 5:20 PM, Christian Lang > <christia...@gmail.com<javascript:>> > wrote: > > 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> 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 tosqlalchemy+...@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.