From: chrisj <chrisj.wood@sympatico.ca>
To: pgsql-performance@postgresql.org
Subject: using a stored proc that returns a result set in a complex SQL stmt
Date: Mon, 15 Oct 2007 09:09:25 -0700 (PDT)
Message-ID: <13216092.post@talk.nabble.com> (raw)
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....
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: chrisj.wood@sympatico.ca
Subject: Re: using a stored proc that returns a result set in a complex SQL stmt
In-Reply-To: <13216092.post@talk.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox