How to include quote in plpgsql function-CodePudding

The following function identifies columns with null values. How can I extend the where clause to check null or empty value?

coalesce(TRIM(string), '') = ''

CREATE OR REPLACE FUNCTION public.is_column_empty(IN table_name varchar, IN column_name varchar)
    RETURNS bool
    LANGUAGE plpgsql
AS $function$
declare 
    count integer;
    BEGIN
execute FORMAT('SELECT COUNT(*) from %s WHERE %s IS NOT NULL', table_name, quote_ident(column_name)) into count;
    RETURN (count = 0);
    END;
$function$
;

CodePudding user response：

You need to double up the quotation marks, like this:

CREATE OR REPLACE FUNCTION public.is_column_empty(IN table_name varchar, IN column_name varchar)
    RETURNS bool
    LANGUAGE plpgsql
AS $function$
declare 
    count integer;
    BEGIN
execute FORMAT('SELECT COUNT(*) from %s WHERE COALESCE(TRIM(%s),'''') <> ''''', table_name, quote_ident(column_name)) into count;
    RETURN (count = 0);
    END;
$function$
;

EDIT:

Re-reading your question, I was a little unsure that you are getting what you want. As it stands the function returns false if at least one row has a value in the given column, even if all the other rows are empty. Is this really what you want, or are you rather looking for columns where any row has this column empty?