Same function in SELECT and WHERE clause

Beginner question:

I have an expensive function f(x, y) on two columns x and y in my database table.

I want to execute a query that gives me the result of the function as a column and puts a constraint on it, something like

SELECT *, f(x, y) AS func FROM table_name WHERE func < 10;

However this doesn't work, so I will have to write something like

SELECT *, f(x, y) AS func FROM table_name WHERE f(x, y) < 10;

Will this run the expensive function twice? What's the best way to do this?

Let's create a function that has a side effect so that we can see how many times it is executed:

CREATE OR REPLACE FUNCTION test.this_here(val integer)
    RETURNS numeric
    LANGUAGE plpgsql
AS $function$
BEGIN
    RAISE WARNING 'I am called with %', val;
    RETURN sqrt(val);
END;
$function$;

And then call this like you do:

SELECT this_here(i) FROM generate_series(1,10) AS t(i) WHERE this_here(i) < 2;

WARNING:  I am called with 1
WARNING:  I am called with 1
WARNING:  I am called with 2
WARNING:  I am called with 2
WARNING:  I am called with 3
WARNING:  I am called with 3
WARNING:  I am called with 4
WARNING:  I am called with 5
WARNING:  I am called with 6
WARNING:  I am called with 7
WARNING:  I am called with 8
WARNING:  I am called with 9
WARNING:  I am called with 10
    this_here     
──────────────────
                1
  1.4142135623731
 1.73205080756888
(3 rows)

As you see, the function is called at least once (from the WHERE clause), and when the condition is true, once again to produce the output.

To avoid the second execution, you can do what Edgar suggests - namely wrap the query and filter the result set:

SELECT * 
  FROM (SELECT this_here(i) AS val FROM generate_series(1,10) AS t(i)) x 
 WHERE x.val < 2;

WARNING:  I am called with 1
... every value only once ...
WARNING:  I am called with 10

To further check how this works, one can go to pg_stat_user_functions and check calls there (given track_functions is set to 'all).

Let's try with something that has no side effect:

CREATE OR REPLACE FUNCTION test.simple(val numeric)
 RETURNS numeric
 LANGUAGE sql
AS $function$
SELECT sqrt(val);
$function$;

SELECT simple(i) AS v 
  FROM generate_series(1,10) AS t(i)
 WHERE simple(i) < 2;
-- output omitted

SELECT * FROM pg_stat_user_functions WHERE funcname = 'simple';
-- 0 rows

simple() is actually too simple so it can be inlined, therefore it does not appear in the view. Let's make it un-inlinable:

CREATE OR REPLACE FUNCTION test.other_one(val numeric)
 RETURNS numeric
 LANGUAGE sql
AS $function$
SELECT 1; -- to prevent inlining
SELECT sqrt(val);
$function$;

SELECT other_one(i) AS v
  FROM generate_series(1,10) AS t(i)
 WHERE other_one(i) < 2;

SELECT * FROM pg_stat_user_functions ;
 funcid │ schemaname │ funcname  │ calls │ total_time │ self_time 
────────┼────────────┼───────────┼───────┼────────────┼───────────
 124311 │ test       │ other_one │    13 │      0.218 │     0.218

SELECT *
  FROM (SELECT other_one(i) AS v FROM generate_series(1,10) AS t(i)) x 
 WHERE v < 2;

SELECT * FROM pg_stat_user_functions ;
 funcid │ schemaname │ funcname  │ calls │ total_time │ self_time 
────────┼────────────┼───────────┼───────┼────────────┼───────────
 124311 │ test       │ other_one │    23 │      0.293 │     0.293

As it looks, the picture is the same with or without side effects.

Changing other_one() to IMMUTABLE changes the behaviour (maybe surprisingly) to the worse, as it will be called 13 times in both queries.

StackExchange Database Administrators Q#196220, answer score: 22