Stored Procedure performance / elegance question

From: "Karen Hill" <karen_hill22(at)yahoo(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Stored Procedure performance / elegance question
Date: 2006-09-08 18:57:54
Message-ID: 1157741874.773922.64550@d34g2000cwd.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Angva 2006-09-08 20:16:37 pg_dump and cluster
Previous Message Michael Schmidt 2006-09-08 18:00:14 Re: PostgreSQL books for beginner