On Sep 20, 2012, at 11:49 AM, David McKeone wrote: > 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! >
Perhaps the easiest way is to create a view: CREATE VIEW table1 AS SELECT * FROM my_function(...); Cheers, M -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To post to this group, send email to sqlalchemy@googlegroups.com. To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
