On Fri, Apr 2, 2010 at 3:41 PM, Michael Bayer <mike...@zzzcomputing.com>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())
>
>
Might this work as a more complete solution for Oracle?
@compiles(coalesce, 'oracle')
def compile(element, compiler, **kw):
sql = "nvl(%s)"
for i in xrange(len(element.clauses) - 2):
sql %= "%s, nvl(%%s)" % compiler.process(element.clauses[i:i+1])
return sql % compiler.process(element.clauses[-2:])
Ian
--
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.
