chrisj wrote: > I am trying to decide between using a temporary table or a stored proc that > returns a result set to solve a fairly complex problem, and was wondering if > Postres, when it sees a stored proc reference in a SQL, is smart enough to, > behind the scenes, create a temporary table with the results of the stored > proc such that the stored proc does not get executed multiple times within a > single query execution?? > > Example: suppose I had a stored proc called SP_bob that returns a result set > including the column store_no > and I wrote the following query: > > select * from Order_Line as X > where not exists (select 1 from SP_bob(parm1, parm2) as Y where X.store_no = > Y.store_no) > > Can I rest assured that the stored proc would only run once, or could it run > once for each row in Order_Line?? It depends on the exact query you're running. I think in the above example, SP_bob would only be ran once. Function volatility affects the planners decision as well (see http://www.postgresql.org/docs/8.2/interactive/xfunc-volatility.html). > The only reason I am going down this road is because of the difficulty of > using temp tables ( i.e. needing to execute a SQL string). Does anyone know > if this requirement may be removed in the near future? I don't understand what requirement you're referring to. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
