Thread: STABLE has no effect on PL/pgsql functions

I noted in my PG 7.4 on Debian Sarge the following behavior:

EXPLAIN ANALYZE SELECT *,reallyHeavyFunctionDefinedAsImmutable() FROM
my_table;
time taken: forever

EXPLAIN ANALYZE SELECT *,
(SELECT reallyHeavyFunctionDefinedAsImmutable())
FROM my_table;
time taken: not very long

First question: Is there a good reason for this difference ?

Second question: If not, when will it be (or was it) fixed ?

Netzach

This characteristic is extremely annoying, given the following:

SELECT * FROM my_table WHERE field1 != ALL (arrayfunc());
  The above reruns arrayfunc() for every line

SELECT * FROM my_table WHERE field1 != ALL
    (SELECT arrayfunc());
ERROR: operator does not exist: interval = interval[]

  The above throws a tantrum, apparently because it considers it to be
an array of arrays.

Netzach

On Fri, 2007-06-01 at 11:54 +0300, psql-novice@netzach.co.il wrote:
> I noted in my PG 7.4 on Debian Sarge the following behavior:

Pg 7.4 is 3 versions out of date.  Even Debian Sarge is no longer Debian
stable; I think you need to get up to date.

> EXPLAIN ANALYZE SELECT *,reallyHeavyFunctionDefinedAsImmutable() FROM
> my_table;
> time taken: forever
>
> EXPLAIN ANALYZE SELECT *,
> (SELECT reallyHeavyFunctionDefinedAsImmutable())
> FROM my_table;
> time taken: not very long

You have to post the full output of both for anyone to have a chance of
explaining the difference.
--
Oliver Elphick                                          olly@lfix.co.uk
Isle of Wight                              http://www.lfix.co.uk/oliver
GPG: 1024D/A54310EA  92C8 39E7 280E 3631 3F0E  1EC0 5664 7A2F A543 10EA
                 ========================================
   Do you want to know God?   http://www.lfix.co.uk/knowing_god.html