> 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 =
> 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
> 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.
In response to
pgsql-performance by date
|Next:||From: Stéphane Schildknecht||Date: 2007-10-16 15:26:15|
|Subject: Re: Vacuum goes worse|
|Previous:||From: Tom Lane||Date: 2007-10-16 15:10:17|
|Subject: Re: Autovacuum running out of memory |