Search Postgresql Archives

Stored Procedure performance / elegance question

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux