RETURN QUERY vs RETURN QUERY EXECUTE

Question

PostgreSQL 39.6. Control Structures: RETURN QUERY has a variant RETURN QUERY EXECUTE, which specifies the query to be executed dynamically. Parameter expressions can be inserted into the computed query string via USING, in just the same way as in the EXECUTE command.

But what means dynamically?

What is a difference between these two statements (inside function with func_param parameter):

RETURN QUERY SELECT * FROM foo WHERE foo_col = func_param;

and

RETURN QUERY EXECUTE 'SELECT * FROM foo WHERE foo_col = $1' USING func_param;

?

Answer 1

The difference between these two examples is that in the first one, the query plan can be retained and reused for future executions in the same database session.

In the second example, the query will be planned every time the function is executed.

The advantage of the first example is that you save planning time if the function is executed repeatedly. From the sixth execution on, PostgreSQL may choose to use a generic plan (see the documentation) which will be use for all future executions in the same database session.

The second example is only useful if the chosen generic plan proves bad, and you'd rather have PostgreSQL replan the query every time.