Search Postgresql Archives

right way of using case-expressions in plpgsql functions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.

 
 

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux