Re: stored procedures and dynamic queries

On Mon, 03 Dec 2007 19:06:29 +0000
Richard Huxton <dev(at)archonet(dot)com> wrote:

> Ivan Sergio Borgonovo wrote:
> > Any general rule about dynamically generated queries in stored
> > procedures vs. performances?
>
> It's the same decision as any with any prepared plan vs
> plan-each-time trade-off.

Should I guess this trade off on aruspices or is it possible to gain
a little bit of culture or it's something based on heuristic and
experience?

If the second, any good reading?
How should I take into account parameters like:
- is it a insert/update vs select query
- is it performed on a large table?
- is it performed frequently? frequently with same parameters?
frequently with different parameters?
- does the table is growing?
- are there frequent delete?
etc...

I'm just guessing things that may impact.

> Of course, in your example the query wouldn't work at all - you'd
> need to use the EXECUTE command in plpgsql.

OK just adapted from a more complicated one and forgot to fix it.

thx

--
Ivan Sergio Borgonovo
http://www.webthatworks.it