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.
