pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedusing a stored proc that returns a result set in a complex SQL stmt
3+ messages / 3 participants
[nested] [flat]
* using a stored proc that returns a result set in a complex SQL stmt
@ 2007-10-15 16:09 chrisj <chrisj.wood@sympatico.ca>
2007-10-16 15:22 ` Re: using a stored proc that returns a result set in a complex SQL stmt Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 1 reply; 3+ messages in thread
From: chrisj @ 2007-10-15 16:09 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: using a stored proc that returns a result set in a complex SQL stmt
2007-10-15 16:09 using a stored proc that returns a result set in a complex SQL stmt chrisj <chrisj.wood@sympatico.ca>
@ 2007-10-16 15:22 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-10-17 05:32 ` Re: using a stored proc that returns a result set in a complex SQL stmt Marcin Stępnicki <mstepnicki@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Heikki Linnakangas @ 2007-10-16 15:22 UTC (permalink / raw)
To: chrisj <chrisj.wood@sympatico.ca>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: using a stored proc that returns a result set in a complex SQL stmt
2007-10-15 16:09 using a stored proc that returns a result set in a complex SQL stmt chrisj <chrisj.wood@sympatico.ca>
2007-10-16 15:22 ` Re: using a stored proc that returns a result set in a complex SQL stmt Heikki Linnakangas <heikki@enterprisedb.com>
@ 2007-10-17 05:32 ` Marcin Stępnicki <mstepnicki@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Marcin Stępnicki @ 2007-10-17 05:32 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2007-10-17 05:32 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-10-15 16:09 using a stored proc that returns a result set in a complex SQL stmt chrisj <chrisj.wood@sympatico.ca>
2007-10-16 15:22 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-10-17 05:32 ` Marcin Stępnicki <mstepnicki@gmail.com>
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