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';

Search Discussions

  • Merlin Moncure at Sep 8, 2006 at 9:04 pm

    On 8 Sep 2006 11:57:54 -0700, Karen Hill 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
  • Karen Hill at Sep 8, 2006 at 9:40 pm

    "Merlin Moncure" wrote:
    On 8 Sep 2006 11:57:54 -0700, Karen Hill 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.
  • Merlin Moncure at Sep 9, 2006 at 12:40 am

    On 8 Sep 2006 14:39:54 -0700, Karen Hill 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
  • Tony Caduto at Sep 12, 2006 at 4:39 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-general @
categoriespostgresql
postedSep 8, '06 at 6:59p
activeSep 12, '06 at 4:39p
posts5
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase