Re: Stored Procedure performance / elegance question

"Merlin Moncure" wrote:
> On 8 Sep 2006 11:57:54 -0700, Karen Hill <karen_hill22(at)yahoo(dot)com> wrote:
> > I know that the planner does not store the plan when EXECUTE is used in
> > a function, but the function looks better when the sql is created
> > dynamically.
>
> my general rule is use static when you can, dynamic when you have to.
> this is a very trivial case which does not get into some of the
> problems with dynamic sql. however, if you are taking parameters that
> alter the actual structure of the query, dynamic might be appropriate.
>
> > FOR rec IN SELECT * FROM test WHERE mydate > $1 LOOP
> > myval := rec.x
> > RETURN NEXT;
> > END LOOP;
> > RETURN;
> > END IF;
>
> you could of course do:
> FOR rec IN SELECT * FROM test WHERE $1 is null or mydate > $1 loop [...]
> or some such.

This was a simple example. In reality, the structure of the query is
altered, but there are about 4 different query possibilities in the
real problem depending on which values are null or not. My question
was is it worth it to use Execute and suffer possible performance
issues of having the planner make a new plan every time the Execute
command was run?

The alternative was to enumerate all 4 possible code execution paths in
the store procedure using conditionals. I assume this is faster in
execution but it looks ugly from a code point of view.

> also, you will get much better performance if you pass back a
> refcursor from the function instead of a setof record. return next is
> not advisable except for very small result sets.
>

Don't refcursors consume a lot of database server resources? I wish to
avoid that so in practice I use LIMIT and OFFSET to control results.