5 Replies Latest reply on Jun 14, 2015 3:42 PM by Mark Addleman

    Feature request - depjoin-like functionality for stored procedures

    Mark Addleman Master

      When modeling an existing API in an VDB, is often much more convenient to use the stored procedure construct than a table.  Ultimately, it is desirable to create a view which joins the stored procedure to something that provides the universe of possible parameter values.  This approach provides the client with a relational view of data and can (mostly) hide the vagaries of the underlying API. The problem with this approach is that many APIs offer a batch facility for retrieving data which cannot be leveraged because Teiid will only push scalar values into a stored procedure.  I'd like to see the dependent join capability to push down multiple values extended to support stored procedures. 

       

      I imagine I'd be able to create a view like:

      CREATE VIEW relational_api

      AS

      SELECT * FROM api_universe, /*+ makedep */ TABLE(CALL api(api_universe.arg1, api_universe_arg2)) api;

       

      The client could execute the query like

      SELECT * FROM relational_api WHERE arg1 < 5

       

      The planner would first execute SELECT * FROM api_universe WHERE arg1 < 5 and then push the batch of results into api stored procedure.