SELECT * FROM function() with declarative

[David McKeone]

I've googled around can't seem to find an answer to this, so hopefully someone knows how to do it here.

I'm using PostgreSQL and I have a PL/PGSQL function that filters and modifies a particular table based on a number of conditions and then returns a set of rows as the result.  This pattern has allowed the system to use the functions as if they were tables so that joins can still be done on the resulting values.

So instead of:

SELECT * FROM table1 INNER JOIN table2 ON table1.t2_id = table2.id

I do:

SELECT * FROM my_function( ...args... ) as table1 INNER JOIN table2 ON table1.t2_id = table2.id

That part works ok in plain SQL (and as well in the system I'm converting from)

So now with SQLAlchemy I have my declarative definitions for those tables:

class Table1(Base):

   __tablename__ = 'table1'

   id = Column()

   t2_id = Column(ForeignKey())

   table2 = Relationship( ... )  # Join condition is specified explicitly

class Table2(Base);

   __tablename__ = 'table2'

 

   id = Column()

and I'm trying to figure out how I would execute a query that looks like this:

result = session.query(Table1).join(Table1.table2).options(contains_eager(Table1.table2))

but using a function to 'fake' Table1 instead.  So basically I'm attempting to get SQLAlchemy to treat the result of my function as if it was the normal Table1 object.  I've tried using select_from() to inject my call to func.my_function() but that doesn't seem to work and since what I'm doing seems like it might be tricky (or not portable across SQL) I thought I'd ask if it's even possible.

Thanks for any help!

[A.M.]

Perhaps the easiest way is to create a view:

CREATE VIEW table1 AS SELECT * FROM my_function(...);

Cheers,
M

[David McKeone]

Hi M,

Is creating something in the database the only way to do it?  How would I coerce the view's return type into my object?  How do I substitute the view in the FROM part of my clause instead?

[Michael Bayer]

you can make a function act like a table by creating a select() out of it, check out the fifth example at http://docs.sqlalchemy.org/en/rel_0_7/core/tutorial.html#functions

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/4EQ3O5IWOX0J.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[David McKeone]

Thanks Mike, That's the part I was looking for!

[David McKeone]

ok, I think I found the solution.  Thought I'd post what I did and what I tried; partly because it might help someone else and partly because someone may have suggestions for a better way.

This is what worked:

my_function = func.my_function(...args...)

func_subquery =  select(['id'], from_obj=[my_function])

results = session.query(Table1).join(Table2).filter(Table1.id.in_(func_subquery))

and results in a query that looks like this:

SELECT table1.id, table1.col1, table1.col2... FROM table1 JOIN table2 ON table1.t2_id = table2.id WHERE table1.id IN( SELECT id FROM my_function(...args...) )

This differs somewhat from what I was initially thinking:

SELECT table1.id, table1.col1, table1.col2... FROM (SELECT * FROM my_function(...args...)) as table1 JOIN table2 ON table1.t2_id = table2.id 

When I run EXPLAIN ANALYZE in PostgreSQL the IN() version seems to be slightly more efficient according to the planner (and real run times are more or less the same)

IN: "Nested Loop  (cost=12.75..889.97 rows=35432 width=222) (actual time=42.200..42.209 rows=2 loops=1)"

JOIN: "Nested Loop  (cost=0.25..4386.37 rows=1000 width=226) (actual time=41.052..41.061 rows=2 loops=1)"

-----------------------------------------------------------------------------

Things I tried on the path to getting my answer:

-----------------------------------------------------------------------------

I attempted to use a combination of a label and an alias with my function (modelled after this: http://docs.sqlalchemy.org/en/rel_0_7/orm/query.html?highlight=aliased#sqlalchemy.orm.aliased):

my_function = func.my_function(...args...).label(Table1.__tablename__)   

my_function_alias = aliased(Table1, alias=my_function, adapt_on_names=True)

but that failed miserably.  Using the labeled function in a regular core select resulted in an AttributeError exception, so I think that might have been part of the problem:

>>> print select('*', from_obj=[my_function])

Traceback (most recent call last):

  File "<stdin>", line 1, in <module>

  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/expression.py", line 1790, in __str__

    return unicode(self.compile()).encode('ascii', 'backslashreplace')

  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/expression.py", line 1778, in compile

    return self._compiler(dialect, bind=bind, **kw)

  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/expression.py", line 1784, in _compiler

    return dialect.statement_compiler(dialect, self, **kw)

  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/compiler.py", line 277, in __init__

    engine.Compiled.__init__(self, dialect, statement, **kwargs)

  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 705, in __init__

    self.string = self.process(self.statement)

  File "/Library/Python/2.7/site-packages/sqlalchemy/engine/base.py", line 724, in process

    return obj._compiler_dispatch(self, **kwargs)

  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/visitors.py", line 72, in _compiler_dispatch

    return getter(visitor)(self, **kw)

  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/compiler.py", line 873, in visit_select

    froms = select._get_display_froms(existingfroms)

  File "/Library/Python/2.7/site-packages/sqlalchemy/sql/expression.py", line 4807, in _get_display_froms

    toremove = set(itertools.chain(*[f._hide_froms for f in froms]))

AttributeError: '_Label' object has no attribute '_hide_froms'

Using the aliased function would include the original table as well as the aliased functioned, and without a join condition it would just do a cartesian product:

>>> results = session.query(my_function_alias).first()

SELECT table1.id, table1.col1, table1.col2... FROM table1, (SELECT * FROM my_function(...args...))

So that didn't work either.  After doing this I realized that if I have to include the table and the function sub-select I might as well attempt it as an IN(), and that is what brought me to my final answer.

Thanks again for your suggestion Mike, it definitely put me on the right path to the solution.

On Thursday, September 20, 2012 7:10:52 PM UTC+2, Michael Bayer wrote: