Re: PostgeSQL JSONB Column with various type of data

On Fri, Dec 4, 2020 at 9:21 AM Riswana Rahman <riswana(at)temenos(dot)com> wrote:

> CREATE OR REPLACE FUNCTION jsonbNull(jsonb_column JSONB)
>
> returns boolean as $$
>
> declare
>
> isPoint text := jsonb_typeof(jsonb_column) ;
>
> begin
>
> CASE isPoint
>
> WHEN 'array' THEN
>
> if true = ALL(select
> (jsonb_array_elements(jsonb_column)) = '{}') THEN
>
> return true;
>
> else
>
> return false;
>
> end if;
>
> WHEN 'object' THEN
>
> if jsonb_column = '{}' THEN
>
> return true;
>
> else
>
> return false;
>
> end if;
>
> WHEN 'string' THEN
>
> return false;
>
> ELSE
>
> return true;
>
> END CASE;
>
> end;
>
> $$ LANGUAGE plpgsql IMMUTABLE;
>

As far as I can tell, it seems like this could be re-written as a function
in SQL instead of plpgsql which allows for it to be in-lined. Have you
tested performance and found it to be an issue, or just optimizing in
advance of a need?

>