In postgresql I have select col1, col2 from my_function(). How can I do that in sqlalchemy core?
select(func.my_function()) gives the result as a string, but I want a tuple.
-
It tends not to play nicely with SPs, I remember giving up on this and running raw commands.Trent– Trent2017-03-31 05:05:35 +00:00Commented Mar 31, 2017 at 5:05
Add a comment
|
1 Answer
You'll want to use FunctionElement.alias() and the light weight column():
from sqlalchemy import func, select, column
stmt = select([column('col1'), column('col2')]).\
select_from(func.my_function().alias())
The documentation specifically mentions Postgresql as a use case for this construct. The above produces:
SELECT col1, col2
FROM my_function() AS anon_1
Using the parameter _selectable of column() you could also:
In [4]: fn = func.my_function().alias()
In [5]: stmt = select([column('col1', _selectable=fn),
...: column('col2', _selectable=fn)])
In [6]: print(stmt)
SELECT anon_1.col1, anon_1.col2
FROM my_function() AS anon_1
but since _selectable is left undocumented, this might not be a good idea.
