STABLE functions

Hi there,

I'm using 7.3.2 and encounter the problem that a SELECT query that uses
a function with a constant argument is quite slow. The function is
declared STABLE. Here's an example:

SELECT ... FROM table WHERE col = f('xyz');

From what I read in the docs

( http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=sql-createfunction.html )

I thought that this function is only executed once when declared as
STABLE, however the time of the query seems to indicate that it is
executed for each row of "table".

Below is a small script that generates some SQL commands that should show
the problem. It creates a table, inserts some rows and defines a
function that should just take some time when executed.

In my opinion

SELECT s FROM test WHERE s = f_test('abc');

should (roughly) be as fast as

SELECT f_test('abc');

but it isn't.

-----8<------------------

#!/bin/sh

echo "CREATE TABLE test (s varchar(300));"

for i in `seq 0 1000`; do
echo "INSERT INTO test VALUES ('$i');"
done

cat << EOF
CREATE OR REPLACE FUNCTION f_test(VARCHAR(200)) RETURNS int AS '
DECLARE
r RECORD;
v VARCHAR(200);
a ALIAS FOR \$1;
BEGIN
IF a IS NULL THEN
RETURN NULL;
END IF;
FOR r IN SELECT * FROM test LOOP
v = r.s;
END LOOP;
RETURN 1;
END;
' LANGUAGE plpgsql STABLE STRICT;

EOF

-----8<------------------

Thanks,
Joachim

--
*****PGP key available - send e-mail request****
If you fail to plan, plan to fail.