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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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].
>>> 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.
>>
>>
>> --
>> 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 [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.
>
>
> --
> 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.
signature.asc
Description: Message signed with OpenPGP using GPGMail
