no wait, scratch my last email for a bit. try rev 2601.
On May 3, 2007, at 5:29 PM, Kirk Strauser wrote:
>
> On May 2, 2007, at 8:32 PM, Michael Bayer wrote:
>
>>
>> oh duh, i forgot about the new thing Gaetan came up with, try this
>> too:
>>
>> mapper(Invoice, invoice_table, properties={
>> 'customer':column_property(func.substr(invoice_table.c.invnum, 1,
>> 4).label('customer'))
>> })
>>
>>
>>
>>
>
> That's so very, *very* close. It works perfectly for that
> particular column, but a slightly more complex function causes
> breakage. The only problem is that it generates SQL that
> PostgreSQL 8.2 isn't quite happy with. Given the following code:
>
> rdy2bill_table = Table('rdy2bill', metadata,
> Column('invid', Integer, primary_key=True),
> Column('invnum', String),
> Column('pprresp', String, key='responsible'),
> Column('xmlvars', String),
> )
>
> mapper(ReadyToBill, rdy2bill_table, properties={
> 'customer' : column_property(func.substr
> (rdy2bill_table.c.invnum, 1,
> 4).label('customer')),
> 'groupcode': column_property(func.substring
> (rdy2bill_table.c.xmlvars, "<M.XRSGRPCD>(.*)</M.XRSGRPCD>").label
> ('groupcode')),
> })
>
> inv = session.query(ReadyToBill).get_by(invid=1000346504,
> groupcode='BILLGSCB')
>
>
> I get a query like:
>
>
> 'SELECT substr(rdy2bill.invnum, %(substr)s, %(substr_1)s) AS
> customer, rdy2bill.xmlvars AS rdy2bill_xmlvars, rdy2bill.pprresp AS
> rdy2bill_pprresp, rdy2bill.invnum AS rdy2bill_invnum,
> rdy2bill.invid AS rdy2bill_invid, substring(rdy2bill.xmlvars, %
> (substring)s) AS groupcode \nFROM rdy2bill \nWHERE (rdy2bill.invid
> = %(rdy2bill_invid)s) AND (substring(rdy2bill.xmlvars, %(substring)
> s) AS groupcode = %(literal)s) ORDER BY rdy2bill.invid \n LIMIT
> 1' {'substring': '<M.XRSGRPCD>(.*)</M.XRSGRPCD>', 'substr': 1,
> 'literal': 'BILLGSCB', 'rdy2bill_invid': 1000346504, 'substr_1': 4}
>
>
> The killer part is the "(substring(rdy2bill.xmlvars, %(substring)s)
> AS groupcode = " in the WHERE clause. PostgreSQL apparently
> doesn't want that predicate to be named. Can that be disabled?
> --
> Kirk Strauser
>
>
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
