x-no-archive:yes

Hello.

I have a stored procedure which returns a setof record. The function
takes a few arguments, and if a couple of specific input values are
null, it is required that the stored procedure perform different
actions.

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.

Which is better? fooA or fooB? :
-- this one looks less elegant but is it faster because the planner
stores the query?
CREATE OR REPLACE FUNCTION fooA (value date , out myval) RETURNS SETOF
RECORD $$
DEFINE
rec RECORD;
BEGIN

IF value IS NULL THEN

FOR rec IN SELECT * FROM test LOOP
myval := rec.x
RETURN NEXT;
END LOOP;
ELSE


FOR rec IN SELECT * FROM test WHERE mydate > $1 LOOP
myval := rec.x
RETURN NEXT;
END LOOP;
RETURN;
END IF;

END ;
$$ LANGUAGE 'plgsql';

Here is fooB:
--code looks cleaner especially when there are more null values to
account for. Is it slower though?
CREATE OR REPLACE FUNCTION fooB(value date , out myval) RETURNS SETOF
RECORD $$
DEFINE
rec RECORD;
str varchar;
BEGIN

IF value IS NULL THEN
str := "SELECT * FROM test";
ELSE
str := "SELECT * FROM test WHERE mydate > ' || quote_literal($1);
END IF;

FOR rec IN EXECUTE str LOOP
myval := rec.x
RETURN NEXT;
END LOOP;

END ;
$$ LANGUAGE 'plgsql';

On 8 Sep 2006 11:57:54 -0700, Karen Hill <karen_hill22@yahoo.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

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

"Merlin Moncure" wrote:
> On 8 Sep 2006 11:57:54 -0700, Karen Hill <karen_hill22@yahoo.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.

On 8 Sep 2006 14:39:54 -0700, Karen Hill <karen_hill22@yahoo.com> wrote:
> "Merlin Moncure" wrote:
> > 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?

you can extend the above to an aribtrary number of parameters:
select * from t where (in_a is null or in_a > a) and (in_b is null or
in_b > b) [...]

the server is very smart at optimizing the above.

however there are some cases where this is inconvenient, for example a
parameter that adds a group by clause. in this case 'execute' is
sometimes better.

The plan generation is only relevant if the function is quick and
generated frequently, or has extremely complex plans (not likely).
There is an interesting recent thread in -hackers discussing plan
generation you might want to check out.

Generally, I don't like dynamic sql because it is more complex to
write and can get very tedius, especially with large queries with lots
of strings. The major time saver with plpgsql, IMO, is that your
queries are first class and dont have to be run through a separate
parser.

> 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.

refcursors are almost always better so long as you remember they can
only be held open for the duration of a transaction. OFFSET on the
other hand is notoriously inefficient as the offsets grow large. It
is fine for small browses however. However, you can just do relative
querying to browse large sets which is probably the fastest way of
all.

merlin

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Karen Hill wrote:
> x-no-archive:yes
>
> Hello.
>
> I have a stored procedure which returns a setof record. The function
> takes a few arguments, and if a couple of specific input values are
> null, it is required that the stored procedure perform different
> actions.
>
> 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.
>
>
Karen,
My particular opinion on this is to only use execute if you need it.
If you don't need it don't use it.
A example where you would need execute is if you wanted to create a new
user from inside a function with a passed in username.
Another example where you would need EXECUTE is if you are working with
temp tables in a function.

Later,

--

Tony Caduto
AM Software Design
http://www.amsoftwaredesign.com
Home of PG Lightning Admin for Postgresql
Your best bet for Postgresql Administration


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend