Postgres JSON NULL not activating parameter default-CodePudding

In PostgreSQL 14, unspecified arguments in a JSON string are interfering with parameter defaults in function calls despite appearing to be NULL.

I believe tst := j::json->>'tst'; is filling tst with a pseudo-null value.

Have I missed something simple? I thought about coalescing into declared variables in testNull but it feels messy and I feel like there must be a more elegant approach.

create or replace function testNull(test varchar, tst int default 1) returns int
language plpgsql as $$
declare
begin 
    
    raise notice '%', tst;
    
    return tst;
end; $$;

----------------------------

create or replace function testNulljson(j varchar) returns int
language plpgsql as $$
declare
    test varchar;
    tst int;
begin 
    
    raise notice '%', tst;
    
    test := j::json->>'test';
    tst := j::json->>'tst';
    return testNull(test, tst);

end; $$;

----------------------------

select testNull('testValue');  -- returns 1

select testNulljson('{"test":"testValue"}'); -- returns null

CodePudding user response：

https://www.postgresql.org/docs/current/ddl-default.html

A column can be assigned a default value. When a new row is created and no values are specified for some of the columns, those columns will be filled with their respective default values.

There is a difference: a. select * from public.testNull('testValue', NULL);
b. select * from public.testNull('testValue' );
a will return NULL, b will return 1;

When you call select testNulljson('{"test":"testValue"}'); You finally is calling select * from public.testNull('testValue', NULL); It will return Null.

when you call select testNull('testValue'); -- returns 1 You didn't mention tst input argument, it will replaced by 1 therefore return 1.