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 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. -- 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.
