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