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 <christian.a.l...@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 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.
signature.asc
Description: Message signed with OpenPGP using GPGMail
