Skip to content

Instantly share code, notes, and snippets.

@portnov
Created June 30, 2022 19:01
Show Gist options
  • Save portnov/5b409a496d8ffd9839c969b7de128fb8 to your computer and use it in GitHub Desktop.
Save portnov/5b409a496d8ffd9839c969b7de128fb8 to your computer and use it in GitHub Desktop.
nvl2 SQL vs pl/pgsql
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