I understood from documentation that case expression can be
1) optimized by planner
2) may compute its subexpressions in advance, in case of presence aggregation functions in them, for example.
The question is - how it is combined with generic prepared plans in pl/pgsql.
How can I deduct - when using case-expression is "safe" regarding query goals with parameters in pl/pgsql and when is not.
There are two cases - expression in select list and expression in where clause.
in where clause:
suppose I have a sql-function like this:
CREATE OR REPLACE FUNCTION nvl_in_where(pf anycompatible, px anycompatible, py anycompatible)
RETURNS boolean
LANGUAGE sql
STABLE
AS $function$
select (case when px is not null then pf = px
else pf is not distinct from py
end);
$function$
;
and then I use it in some pl/pgsql function:
CREATE OR REPLACE FUNCTION plsql_query_function(in pn numeric )
RETURNS boolean
LANGUAGE plpgsql
STABLE
AS $function$
Declare
sr record;
Begin
For sr in Select tbl.p1,tbl.p2
From tbl
Where
nvl_in_where(tbl.p1, pn, tbl.p1)
Loop
-- do some logic with sr ...
-- ...
Null;
end loop;
end;
$function$
;
If execute this query individually with fixed value of parameter $1, the query plan
would be like
Select tbl.p1,tbl.p2
From tbl
Where
tbl.p1 = pn::numeric
;
or
Select tbl.p1,tbl.p2
From tbl
Where
tbl.p1 is not distinct from tbl.p1
;
depending if pn is null or not.
The documentation states that after some executions of such functions the plan should become generic.
What is a generic plan for such a case and how would it work?
If it is safe to use function like nvl_in_where in where clause of queries in plpgsql function or,
maybe, I should only use that query only with the execute statement in plpgsql?
in select list:
suppose, i need something like this :
select case $1
when '1'::numeric then tbl.p1
when '2'::numeric then tbl.p2
...
end as cresult
from tbl ...
Can I be sure, that this expression would not be "optimised" in generic plan just to
select
tbl.some_lucky_fied
from tbl
Can I use this type of expression in the select list regarding generic plans
or should I prefer dynamic execution for such type queries in plpgsql?
Thanks in advance.
PS
The question looks like from a novice, and , indeed, I am.