DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:user-agent:mime-version:to:cc:subject:references:in-reply-to:content-type:content-transfer-encoding:sender;
	b=JqWS0Du0uWBfjw/v6a1yS4k4CKJxcgxDRVuCNtXe2qQXXXDl3/Kd3yH1DfxI9cO8QXjJB2rr+TCABfrtpSsuMmInJ1EJYtQnwVDF6cuq3ElkGZrimXUpLzLfhaMWtRblR/iPhMoan1HUDbDgPh0kl7CDMPq6u8flrtV3nkqHZDg=
Message-ID: <4714D748.6020801@enterprisedb.com>
Date: Tue, 16 Oct 2007 16:22:48 +0100
From: Heikki Linnakangas <heikki@enterprisedb.com>
User-Agent: Icedove 1.5.0.12 (X11/20070730)
MIME-Version: 1.0
To: chrisj <chrisj.wood@sympatico.ca>
CC: pgsql-performance@postgresql.org
Subject: Re: using a stored proc that returns a result set in a
	complex SQL stmt
References: <13216092.post@talk.nabble.com>
In-Reply-To: <13216092.post@talk.nabble.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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