| 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: | Whole Thread | Raw Message | 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';
| 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 |