generic functions

[Mike Bayer]

func should be enhanced at the ANSICompiler / dialect level to support generic functions. functions such as concat() (#475) and date_trunc() would be available off of func like any other function. ANSICompiler would be able to receive a "registry" of common function conversions from each dialect in order to render the SQL version of the function...if no registered name is present, the function is used as is. we can do this now except that I'd like the "list of names" to be formalized in ANSICompiler so that all the dialects have a target to shoot for.

the registry would probably need to return either string templates or callables, since the variety of functional formats varies greatly (i.e. needing special constants, argument conversions, etc).

we also would need to decide what names SQLAlchemy should standardize upon.

[Mike Bayer]

just to clarify - the idea here is that func.date_trunc() would render as whatever the equivalent is on a particular dialect. an example of the current version of this logic can be found in mssql.py:

    function_rewrites =  {'current_date': 'getdate',
                          'length':     'len',
                          }
    def visit_function(self, func):
        func.name = self.function_rewrites.get(func.name, func.name)
        super(MSSQLCompiler, self).visit_function(func)

thats the basic idea. but a function like date_trunc() in particular has bigger needs due to the argument conversion:

postgres:

date_trunc('hour', somecolumn)

oracle:

TO_CHAR(somecolumn, 'HH')

# needs to convert to integer to be equivalent to PG ?
cast(TO_CHAR(somecolumn, 'HH')) AS INTEGER

etc. so we'd have to dig into the arguments of the function and convert their value at the bind param level too.

[Mike Bayer]

do something similar to the Operators class. make _Function into something first class like Function, add tokens to it for all the functions we know of, and implement along those lines. the "callable" approach should allow tricky ones like oracle TO_CHAR and such to have a chance at working. replace the "ANSI_FUNCS" collection with a genericized approach here.

[jek]

a dialect may need to tailor these to the remote server version....

[Mike Bayer]

ensure that the functions know how to apply basic typing rules, such as func.max() should return the datatype of its first argument.

[Former user]

Probably function need to be matched not only by name. E.g. MAX() function, it (probably) in all databases works for aggregations. Also in SQLite MAX(1,2) gives result 2, but formost databases I believe there is a greatest() and least() functions for that purpose (at least MySQL and PostgreSQL).

Here number of arguments work. I believe that there is not much aggregation functions at all, and probably only these two need to be special-cased.

[Mike Bayer]

the basic idea is committed in 238c2c8dbe3ca5b92d298b39e96f81eb416d1413. some highlights:

argument signatures can be checked. the below raises an error since char_len takes only one argument:

func.char_len("x", "y")

typing works pretty well. the below expression knows that it returns a Date:

func.coalesce(datetime.date(2007, 10, 5), datetime.date(2005, 10, 15))

although this depends on a bindparameter-to-type mapping in _BindParamClause which should be generalized into the types module.

dialects can of course override the compilation of functions as they do with operators, using a dictionary of strings and/or callables mapped to function classes in the functions module.

if we need to support things like MAX does one thing for one DB, and something else for others, thats exactly where we would add a new function to represent the unique behavior, since the idea of generic functions is, you call func.max(), and it always does the same thing across all DBs.

the functions in the functions module can be called directly, so if we wanted we could also have more functions available in dialects, like oracle.to_char() etc., though im not sure what the advantages of that would be.

[Mike Bayer]

Removing milestone: 0.4.xx (automated comment)