2010/8/29 Tom Lane <t...@sss.pgh.pa.us>: > Pavel Stehule <pavel.steh...@gmail.com> writes: >> 2010/8/29 Tom Lane <t...@sss.pgh.pa.us>: >>> The one case is inline-able and the other not (because it would result >>> in double evaluation of the volatile function random()). >>> See EXPLAIN VERBOSE. > >> I understand now. So it means general advice - don't use a boolean >> operators in SQL function? This issue should be documented somewhere? > > It has nothing to do with boolean operators, just double evaluation. >
sure. I was blind. I have a question. It is possible do following optimalisation? I can write a function CREATE OR REPLACE FUNCTION estring(text) RETURNS bool AS $$ SELECT x IS NULL || x = '' FROM (VALUES($1)) g(x) $$ LANGUAGE sql; Now this function isn't inlined, because optimaliser doesn't know a VALUES clause. But with this knowleade, this can be a protection before double evaluation. Or different way - generate_subplan with parameters - it is still faster, than plpgsql or not inlined sql. p.s. this query is badly planed postgres=# select sum((select x is null or x = '' from (values(CASE WHEN random() < 0.5 THEN NULL ELSE 'x' END)) g(x) )::int) FROM generate_series(1,100000); sum -------- 100000 (1 row) for corect behave a had to append a second variable postgres=# select sum((select x is null or x = '' and i = i from (values(CASE WHEN random() < 0.5 THEN NULL ELSE 'x' END)) g(x) )::int) FROM generate_series(1,100000) x(i); sum ------- 50036 (1 row) Regards Pavel Stehule > regards, tom lane > -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers