Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
select version();


version


PostgreSQL 15.0 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-10), 64-bit



SELECT 1


create table patient( id   integer  generated always as identity
                                     primary key 
                    , first_name text
                    , last_name  text
                    ) ; 
                
insert into patient(first_name, last_name)
   values ('f1','l1'),('f2','l2'),('f3','l3'),('f4','l4'), ('f5', 'l5') ; 
  
select * 
  from patient;

CREATE TABLE



INSERT 0 5




id
first_name
last_name


1
f1
l1

2
f2
l2

3
f3
l3

4
f4
l4

5
f5
l5



SELECT 5


/* ========================================
   Instead of repeating the query several times I wrap it in a Procedure. This is NOT necessary, 
   but then again is NOT process to follow.
========================================*/

create or replace procedure update_patient_name
                          ( id_in          integer
                          , first_name_in  text default null
                          , last_name_in   text default null
                          )
 language sql 
as $$
    update patient 
       set first_name = nullif(coalesce(first_name_in,first_name),'set name to null') 
         , last_name  = nullif(coalesce(last_name_in,last_name),'set name to null')
     where id = id_in; 
$$; 

CREATE PROCEDURE


do $$
begin
   call update_patient_name(1, null, 'l1-1');                       -- change last name 
   call update_patient_name(2, 'f2-1', null);                       -- change first name 
   call update_patient_name(3, 'f3-1', 'l3-1');                     -- change both 
   call update_patient_name(4, 'set name to null',null);            -- set first name null
   call update_patient_name(5, last_name_in =>'set name to null');  -- set null name null
end ; 
$$; 
   
select * 
  from patient;

DO




id
first_name
last_name


1
f1
l1-1

2
f2-1
l2

3
f3-1
l3-1

4
null
l4

5
f5
null



SELECT 5