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.
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.