Hi,
In PostgreSQL 9.2, I have the following behavior, and I found it strange.
ps3 is executed or "never executed" ? !!!
JG
[postgres@]test=# create or replace function ps3(a int) returns int as $$ BEGIN
RAISE WARNING 'Call ps3(%)=%',$1,$1; RETURN $1::int;
END; $$ LANGUAGE plpgsql STRICT IMMUTABLE;
CREATE FUNCTION
Temps : 22,632 ms
[postgres@]test=# select coalesce( (select ps3(1)), (SELECT ps3(2)) );
WARNING: Call ps3(1)=1
WARNING: Call ps3(2)=2
coalesce
----------
1
(1 ligne)
Temps : 0,692 ms
[postgres@]test=# select coalesce( ps3(1), ps3(2) );
WARNING: Call ps3(1)=1
coalesce
----------
1
(1 ligne)
Temps : 0,441 ms
[postgres@]test=# explain (analyze, verbose, buffers) select coalesce( (select ps3(1)), (SELECT ps3(2)) );
WARNING: Call ps3(1)=1
WARNING: Call ps3(2)=2
QUERY PLAN
--------------------------------------------------------------------------------------------
Result (cost=0.02..0.03 rows=1 width=0) (actual time=0.006..0.006 rows=1 loops=1)
Output: COALESCE($0, $1)
InitPlan 1 (returns $0)
-> Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.001..0.001 rows=1 loops=1)
Output: 1
InitPlan 2 (returns $1)
-> Result (cost=0.00..0.01 rows=1 width=0) (never executed)
Output: 2
Total runtime: 0.024 ms
(9 lignes)
Temps : 0,819 ms