Re: [sqlalchemy] Mixing ORM and set-returning-functions (e.g. generate_series)

Michael Bayer Wed, 13 Nov 2013 16:49:29 -0800

On Nov 13, 2013, at 6:12 PM, Daniel Grace <[email protected]> wrote:

I know it's been a few days, but I've finally had time to actually get back to this

On Thursday, November 7, 2013 6:18:57 PM UTC-8, Michael Bayer wrote:
On Nov 7, 2013, at 6:46 PM, Daniel Grace <[email protected]> wrote:
[...]
that’s basically equivalent. If you want the identical syntax with that function, the @compiles construct would be a start, but you’d be probably making a custom FromClause subclass which is a little involved.

I'd think that a generic form of this might actually be a way to go, but I'm not (yet) familiar enough with sqlalchemy's internals to make a lot of headway in designing any 'sane' solution.

It's worth noting PostgreSQL supports column-level aliasing on anything, not just a set-returning-function, such that the following is valid:

CREATE TABLE foo ( id SERIAL NOT NULL);
INSERT INTO foo ( id ) VALUES (1), (2), (3);
SELECT bar.baz FROM foo AS bar(baz);

That said, it's not cleanly usable in most cases -- you can't alias a column by name, so you have to know the exact order columns appear in (which you might not know if you didn't reflect nor create the table -- a column definition that doesn't appear in the model might be in the table, for instance).

That said, it'd seem like a generic "Set Returning Function" implementation would need to do the following:
* Subclass from GenericFunction to track data types of input values and whatever voodoo is required for bind parameters. (I think this also gives us the behavior of "SELECT generate_series(...)" being treated like "SELECT FROM ... generate_series(...)..." for free)
* Subclass from FromClause or perhaps even Alias to track types and names of output values in self.c
* Have an alternate/extended 'alias' implementation capable of defining aliases on a per-column level, so AS alias becomes AS alias(column_alias, ...)

So usage might be something like:

series = sql.srfunc.generate_series(1, 10, output=[Column('value', Integer()])
foo = series.alias("foo").column_alias('value', 'v')
bar = foo.alias("bar") # I'm assuming that aliasing an existing alias Does The Right Thing(tm), I've never tried it.

session.query(series.c.value)
# SELECT generate_series.value FROM generate_series(1, 10) AS generate_series(value)

session.query(foo.c.v)
# SELECT foo.v FROM generate_series(1, 10) AS foo(v)

session.query(foo.c.v + bar.c.v).select_from(foo).join(bar, foo.c.v < foo.c.v.)
# SELECT foo.v + bar.v FROM generate_series(1, 10) AS foo(v) JOIN generate_series(1, 10) AS bar(v) ON foo.v < bar.v;

see attached for demo, should get you started

from sqlalchemy.sql.selectable import FromClause

from sqlalchemy import select, func, Column, Integer
from sqlalchemy.ext.compiler import compiles


class PGAsFunc(FromClause):
    named_with_column = True

    def __init__(self, func, columns, alias=None):
        self.func = func
        self._column_args = columns
        self.name = alias or func.name
        self._from_objects = [self]

    def alias(self, name):
        return PGAsFunc(self.func, self.c, name)

    def column_alias(self, from_, to):
        pg = PGAsFunc(
                    self.func,
                    self.c,
                    self.name)
        # this isn't really a built-in operation so klunk it through
        pg.c
        pg._columns[to] = pg._columns[from_]._make_proxy(pg, to)
        pg._columns.remove(pg.c[from_])
        return pg

    def _populate_column_collection(self):
        for c in self._column_args:
            c._make_proxy(self)

class MyFuncThing(object):
    def __getattr__(self, key):
        def create_function(*args, **kw):
            output = kw['output']
            return PGAsFunc(getattr(func, key)(*args), output)
        return create_function


@compiles(PGAsFunc)
def compile(element, compiler, **kw):
    col_kwargs = kw.copy()
    col_kwargs['include_table'] = False
    return "%s AS %s(%s)" % (
        compiler.process(element.func, **kw),
        element.name,
        ", ".join(
            compiler.process(expr, **col_kwargs)
            for expr in element.c
        )
    )


series = MyFuncThing().generate_series(1, 10, output=[Column('value', Integer())])
foo = series.alias("foo").column_alias('value', 'v')
bar = foo.alias("bar")  # I'm assuming that aliasing an existing alias Does The Right Thing(tm), I've never tried it.

from sqlalchemy.orm import Session
session = Session()

print session.query(series.c.value)
print session.query(foo.c.v)
print session.query(foo.c.v + bar.c.v).select_from(foo).join(bar, foo.c.v < bar.c.v)

Thoughts?

-- Daniel

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/groups/opt_out.

signature.asc
Description: Message signed with OpenPGP using GPGMail

Re: [sqlalchemy] Mixing ORM and set-returning-functions (e.g. generate_series)

Reply via email to