Hi, Apparently scalar subquery when used as a part of SELECT statement and when it does not depend on outer query columns is executed only once per statement, e.g.:
postgres=# select i, (select random()) rand from generate_series(1, 3) i; i | rand ---+------------------- 1 | 0.992319826036692 2 | 0.992319826036692 3 | 0.992319826036692 (Though term "depend" is subtle, compare these: postgres=# select i, (select random() + case when false then i else 0 end ) rand from generate_series(1, 3) i; i | rand ---+------------------- 1 | 0.806265413761139 2 | 0.806265413761139 3 | 0.806265413761139 (3 rows) postgres=# select i, (select random() where i=i ) rand from generate_series(1, 3) i; i | rand ---+------------------- 1 | 0.426443862728775 2 | 0.133071997668594 3 | 0.751982506364584 (3 rows) postgres=# select i, (select random() where i=i or i is null ) rand from generate_series(1, 3) i; i | rand ---+------------------- 1 | 0.320982406847179 2 | 0.996762252878398 3 | 0.076554249972105 (3 rows) Looks like dependence is not there anymore if PG is smart enough to simplify boolean expressions) Anyway, as some older PG versions and Oracle behave similarly I suppose this result is expected and desired (correct?), but unfortunately not well-documented (did I miss it mentioned?). Can anyone shed some light on this and/or probably update docs? P.S. I got bitten by a statement like this: select (select nextval('someseq') * a + b from somefunc()), col1, .... with a and b being OUT parameters of somefunc().