Re: stored procedures and dynamic queries

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.

A query built using EXECUTE will have to be planned each time. That
costs you something but means the plan will have all the information it
needs.

A pre-planned query saves planning time on the second,third etc runs but
the plan won't change with the query-parameters.

So:
A query that's going to be executed a million times in a loop with the
same plan each time implies you want a pre-planned query.

A query executed once, or where changing input parameters would benefit
from changing plans implies you want to re-plan each time.

If you don't have a good reason to think it matters one way or the
other, then it probably doesn't. If it's not worth testing then it's not
worth optimising either.

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

--
Richard Huxton
Archonet Ltd