Thread: using a stored proc that returns a result set in a complex SQL stmt
Hi, 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?? 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? -- View this message in context: http://www.nabble.com/using-a-stored-proc-that-returns-a-result-set-in-a-complex-SQL-stmt-tf4628555.html#a13216092 Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
Re: using a stored proc that returns a result set in a complex SQL stmt
From
Heikki Linnakangas
Date:
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
Re: using a stored proc that returns a result set in a complex SQL stmt
From
"Marcin Stępnicki"
Date:
On 10/16/07, Heikki Linnakangas <heikki@enterprisedb.com> wrote: > > 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. I think he means creating temporary tables in stored procedures as described for example here -> http://svr5.postgresql.org/pgsql-sql/2007-01/msg00117.php . From what I see at http://www.postgresql.org/docs/8.3/static/release-8-3.html the EXECUTE workaround is no longer necessary as plan invalidates upon table schema changes.