By refering to http://archives.postgresql.org/pgsql-general/2010-01/msg00846.php
It solves speed problem in stored procedure, which use function parameter, during its SQL query.
Does this means, I shall convert *ALL* my stored procedure, which use function parameter during its SQL query, to use
EXECUTE,to ensure I always get index-scan?
For example :
convert :
CREATE OR REPLACE FUNCTION update_lot_end_timestamp(bigint)
RETURNS timestamp AS
$BODY$DECLARE
_lotID ALIAS FOR $1;
_timestamp timestamp;
BEGIN
_timestamp = now();
UPDATE lot SET timestamp = _timestamp WHERE lot_id = _lotID;
return _timestamp;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION update_lot_end_timestamp(bigint) OWNER TO postgres;
to
CREATE OR REPLACE FUNCTION update_lot_end_timestamp(bigint)
RETURNS timestamp AS
$BODY$DECLARE
_lotID ALIAS FOR $1;
_timestamp timestamp;
BEGIN
_timestamp = now();
EXECUTE 'UPDATE lot SET timestamp = $1 WHERE lot_id = $2'
USING _timestamp, _lotID;
return _timestamp;
END;$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
ALTER FUNCTION update_lot_end_timestamp(bigint) OWNER TO postgres;
Is there any rule of thumb to follow?
Thanks and Regards
Yan Cheng CHEOK