Skip to content

Instantly share code, notes, and snippets.

@Komzpa
Created October 19, 2016 11:32
Show Gist options
  • Save Komzpa/a4f659743eb309fdab74bbdde2b18ded to your computer and use it in GitHub Desktop.
Save Komzpa/a4f659743eb309fdab74bbdde2b18ded to your computer and use it in GitHub Desktop.
compare case and nullif
create or replace function aaa(aaa integer)
returns text as $$
begin
raise notice 'passed1';
return aaa :: text || '😼';
end
$$ language 'plpgsql' strict;
select case when aaa(1) = '1'
then null
when aaa(1) = '3'
then null
else aaa(1) end;
select nullif(nullif(aaa(1), '1'), '3');
[local] gis@gis=# create or replace function aaa(aaa integer)
[more] - > returns text as $$
[more] $ > begin
[more] $ > raise notice 'passed1';
[more] $ > return aaa :: text || '😼';
[more] $ > end
[more] $ > $$ language 'plpgsql' strict;
CREATE FUNCTION
Time: 0,672 ms
[local] gis@gis=#
[local] gis@gis=# select case when aaa(1) = '1'
[more] - > then null
[more] - > when aaa(1) = '3'
[more] - > then null
[more] - > else aaa(1) end;
NOTICE: 00000: passed1
LOCATION: exec_stmt_raise, pl_exec.c:3165
NOTICE: 00000: passed1
LOCATION: exec_stmt_raise, pl_exec.c:3165
NOTICE: 00000: passed1
LOCATION: exec_stmt_raise, pl_exec.c:3165
β”Œβ”€β”€β”€β”€β”€β”
β”‚ aaa β”‚
β”œβ”€β”€β”€β”€β”€β”€
β”‚ 1😼 β”‚
β””β”€β”€β”€β”€β”€β”˜
(1 row)
Time: 0,492 ms
[local] gis@gis=#
[local] gis@gis=# select nullif(nullif(aaa(1), '1'), '3');
NOTICE: 00000: passed1
LOCATION: exec_stmt_raise, pl_exec.c:3165
β”Œβ”€β”€β”€β”€β”€β”€β”€β”€β”
β”‚ nullif β”‚
β”œβ”€β”€β”€β”€β”€β”€β”€β”€β”€
β”‚ 1😼 β”‚
β””β”€β”€β”€β”€β”€β”€β”€β”€β”˜
(1 row)
Time: 0,292 ms
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment