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]<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 [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.
