Functions as FROM clauses

[Former user]

Used-defined functions (a.k.a, database "stored procedures" that are internal, query language, extended language, etc.) may return a "scalar" value which is essentially a one-column, one-row result. However, functions that return a result that is a SETOF or ROW type do not produce the intended result object when using the SQLAlchemy "func" notation. In the example below assume that listcust() is a PostgreSQL function that returns a set and not a "scalar" type, that is, not a one-row, one-column base type:

qs=db.func.listcust()

str(qs)

'SELECT listcust()'

The above is the typical query string composition for a "func" call in SQLAlchemy. If the returned result of the function is a composite or set (table) type then this query string composition does not produce the intended result object. It produces a single column, multi-row result of "row-tuples" converted to string objects.

The PostgreSQL documentation states that for functions that return composite or set type results, the function should be called as in:

'SELECT * FROM listcust()'

This ensures that the query is resolved into columns instead of a single column of row (or composite) values.

It appears that this style query string composition works for functions that return "scalar" as well as row (composite) and set (table) values. That is,

'SELECT now()'

returns the same result as

'SELECT * FROM now()'

At least that is the case with PostgreSQL.

If this is the case then there it seems that in order to have the SQLAlchemy "func" notation applicable regardless of the type of function result, that a change to the query string composition for these "func" calls would be a useful enhancement and avoid the situation described above.

I suggest that the query string composition for SQLAlchemy "func" calls be changed from the current to the form described above as:

'SELECT * FROM func()'

I am new to SQLAlchemy and so if there is some way within the existing syntax to differentiate between "scalar" and "non-scalar" result types produced by database user-defined function calls, then I apologize. I would be interested in an example of the proper syntax in that case.

Thank you for considering this suggestion.

David E. Miller

[e-mail]

[Mike Bayer]

i think your suggestion is a great idea and is very easy to implement. it has to be tested on the "big 4" (sqlite,oracle,mysql,postgres) to make sure it can work across the board (otherwise exception cases will have to be coded for specific DBs).

[Mike Bayer]

id like to get this done sometime between 0.2.2 and 0.2.5 so i am 0.3.0 milestoning it. its not hard, but id want to do it carefully, make sure the unittests test it, maybe even create a failure condition that breaks the old way, etc.

[Mike Bayer]

now we're thinking along the lines of this:

f = Function('diary',
        Parameter('iddip', Integer),
        Parameter('fromdate', Date),
        Parameter('todate', Date),
        Parameter('live', Boolean),
        Column('result_col', String),
        Column('result_date', Date)
    )

    f(5,datetime(5,2,2006), datetime(5,5,2006), True).select()

i.e. a new Schema object. the result of calling f(...) will probably produce a Function or SelectableFunction type of object.

[Former user]

PostgreSQL also supports a return type of RECORD for which the name, type and number of the columns are determined at call time. I'm not sure if other databases support things like this.

Could you divulge more of the proposed implementation details?

[Mike Bayer]

unfortunately it appears that both sqlite and mysql do not support the syntax of "select * from function". so the original idea cant work. also aaron's note about postgres with modifiable column results leads me to see a little bit of overkill in the "functional from" approach.

so in changeset:1946 I have modified the semantics of Function so that it behaves correctly when added to the from_obj parameter of a select. while these examples only work in postgres (and maybe oracle), you can now do:

s = select(['*']('*'), from_obj=[func.my_func()](func.my_func()))
s = select([  column('col1', type=Integer),
   column('col2', type=String)
   ](
), from_obj=[func.my_func()](func.my_func()))

and the resulting Select objects, particularly the second one with the named columns, are usable in the same way as tables (i.e. they have a "c" attribute, etc.):

    result = s.execute()
    for row in result:
       print row[s.c.col1](s.c.col1), row[s.c.col2](s.c.col2)

[Mike Bayer]

Removing milestone: 0.3.0 (automated comment)