Created
June 30, 2022 19:01
-
-
Save portnov/5b409a496d8ffd9839c969b7de128fb8 to your computer and use it in GitHub Desktop.
nvl2 SQL vs pl/pgsql
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
create or replace function nvl2(expr1 anycompatible, expr2 anycompatible, expr3 anycompatible) returns anycompatible as $$ | |
select case when expr1 is null then expr2 else expr3 end; | |
$$ language sql immutable; | |
create or replace function nvl2pg(expr1 anycompatible, expr2 anycompatible, expr3 anycompatible) returns anycompatible as $$ | |
begin | |
if expr1 is null then | |
return expr2; | |
else | |
return expr3; | |
end if; | |
end; | |
$$ language plpgsql immutable; | |
explain (analyze,verbose) | |
select nvl2(custamt::text, 'set', 'unset') from tran; | |
--Append (cost=0.00..18598.96 rows=493074 width=32) (actual time=0.019..129.488 rows=492929 loops=1) | |
-- -> Seq Scan on public.tran_initial tran_1 (cost=0.00..0.01 rows=1 width=32) (actual time=0.005..0. | |
-- Output: CASE WHEN ((tran_1.custamt)::text IS NULL) THEN 'set'::text ELSE 'unset'::text END | |
explain (analyze,verbose) | |
select nvl2pg(custamt::text, 'set', 'unset') from tran; | |
--Append (cost=0.00..141867.46 rows=493074 width=32) (actual time=125.141..681.455 rows=492929 loops=1) | |
-- -> Seq Scan on public.tran_initial tran_1 (cost=0.00..0.26 rows=1 width=32) (actual time=0.008..0.008 rows=0 loops=1) | |
-- Output: nvl2pg((tran_1.custamt)::text, 'set'::text, 'unset'::text) |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment