Kent Bower wrote:
> As an aside, more recent Oracles support ansi "coalesce",
> so it is probably more appropriate to only use nvl() for older, non-ansi
> Oracle versions, but if this is only for illustration then that is not a
> big deal (unless you don't want people writing to you saying coalesce
> does support more than 2 arguments.
>
> In my case, I'd rather rework the "nvl()" function to work on other
> engines besides Oracle as an alias to "coalesce()", seems less ambiguous.
ultimately the oracle dialect should do the same thing we want it to do
for "now()" here. return nvl() on oracle 8.
>
> Thanks again.
>
>
>
> On 4/2/2010 5:41 PM, Michael Bayer wrote:
>> Kent wrote:
>>
>>> Along the same lines, is there something we can do about nvl()
>>> (oracle) versus coalesce() (ansi)?
>>>
>>> They aren't exactly the same, unfortunately (nvl takes exactly 2
>>> arguments, no more), so maybe there is nothing 'official' you can do,
>>> but can you help me work it out for my project?
>>>
>>> I assume it is very similar to what you helped me out with
>>> above...something like this:
>>>
>>> ============================================================
>>> from sqlalchemy.sql.expression import ColumnElement
>>> from sqlalchemy.ext.compiler import compiles
>>> from sqlalchemy.types import DateTime, Date
>>>
>>> class current_date(ColumnElement):
>>> type = Date()
>>>
>>> @compiles(current_date)
>>> def _compiler_dispatch(element, compiler, **kw):
>>> if compiler.dialect.name == 'oracle':
>>> if compiler.dialect.use_ansi:
>>> return "trunc(current_date)"
>>> else:
>>> return "trunc(sysdate)"
>>> else:
>>> # assume postgres
>>> return "current_date"
>>> ============================================================
>>>
>>>
>>> But the main difference is it takes arguments.
>>>
>>> Is there a clever way to return the appropriate function, something
>>> like this:
>>>
>>> def ....
>>> if compiler.dialect.name == 'oracle':
>>> return func.nvl
>>> else:
>>> # assume postgres
>>> return func.coalesce
>>>
>> I will add this to the docs:
>>
>>
>> from sqlalchemy import *
>> from sqlalchemy.ext.compiler import compiles
>>
>> from sqlalchemy.sql.expression import FunctionElement
>>
>>
>> class coalesce(FunctionElement):
>> name = 'coalesce'
>>
>> @compiles(coalesce)
>> def compile(element, compiler, **kw):
>> return "coalesce(%s)" % compiler.process(element.clauses)
>>
>>
>> @compiles(coalesce, 'oracle')
>> def compile(element, compiler, **kw):
>> if len(element.clauses)> 2:
>> raise TypeError("coalesce only supports two arguments on
>> Oracle")
>> return "nvl(%s)" % compiler.process(element.clauses)
>>
>> print coalesce(5, 6)
>>
>> from sqlalchemy.dialects import oracle
>> print coalesce(5, 6).compile(dialect=oracle.dialect())
>>
>>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com.
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
