Function cachable is not anymore inside a function !!!!

Hi all,

take a look a this function:

CREATE FUNCTION sp_sm_status_user ( TEXT )
RETURNS INTEGER AS'
DECLARE
   a_status ALIAS FOR $1;
   my_counter   INTEGER;
BEGIN

   SELECT INTO my_counter count(*)
   FROM user_data
   WHERE id_user_status = sp_lookup_id(''user_status'', a_status);

   RETURN my_counter;

END;
' LANGUAGE 'plpgsql';


now take a look a these explain:

# explain analyze select sp_sm_status_user('Active');
NOTICE:  QUERY PLAN:

Result  (cost=0.00..0.01 rows=1 width=0) (actual time=5004.57..5004.57
rows=1 loops=1)
Total runtime: 5004.60 msec

EXPLAIN


if I do the same query inside the function:

# explain analyze SELECT count(*)
                          FROM user_data
                          where id_user_status = sp_lookup_id('user_status',
'Active');


NOTICE:  QUERY PLAN:

Aggregate  (cost=271.14..271.14 rows=1 width=0) (actual time=27.29..27.29
rows=1 loops=1)
  ->  Seq Scan on user_data  (cost=0.00..259.65 rows=4596 width=0) (actual
time=0.03..21.64 rows=4592 loops=1)
Total runtime: 27.35 msec

EXPLAIN

The function sp_lookup_id  is cachable but is seems that is not used
If  I store the result in a variable all behaviour change:

CREATE OR REPLACE FUNCTION sp_sm_status_user ( TEXT )
RETURNS INTEGER AS'
DECLARE
   a_status ALIAS FOR $1;
   my_counter   INTEGER;
   my_value       INTEGER;
BEGIN

   my_value := sp_lookup_id(''user_status'', a_status);

   SELECT INTO my_counter count(*)
   FROM user_data
   WHERE id_user_status = my_value;

   RETURN my_counter;

END;
' LANGUAGE 'plpgsql';




Ciao
Gaetano