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