Re: Stored Procedure performance / elegance question

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.

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.

merlin