Thanks Mike, That's the part I was looking for!
On Thursday, September 20, 2012 7:10:52 PM UTC+2, Michael Bayer wrote: > > 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 > > On Sep 20, 2012, at 1:09 PM, David McKeone wrote: > > 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? > > > On Thursday, September 20, 2012 5:52:28 PM UTC+2, A.M. wrote: >> >> >> 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 view this discussion on the web visit > https://groups.google.com/d/msg/sqlalchemy/-/4EQ3O5IWOX0J. > To post to this group, send email to [email protected]<javascript:> > . > To unsubscribe from this group, send email to > [email protected] <javascript:>. > For more options, visit this group at > http://groups.google.com/group/sqlalchemy?hl=en. > > > -- 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/-/eWIe2E7mJYkJ. To post to this group, send email to [email protected]. To unsubscribe from this group, send email to [email protected]. For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
