How to include quote in plpgsql function

kitchenprinzessin 0
Viewed 51

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$
;
plpgsql dynamic-sql sql postgresql
2 Answers

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?

Jonathan Willcock0

There are more possibilities - for example you can use custom string separators:

CREATE OR REPLACE FUNCTION public.is_column_empty(IN table_name varchar,
                                                  IN column_name varchar)
  RETURNS bool
LANGUAGE plpgsql
AS $function$
 DECLARE _found boolean; /* attention "count" is keyword */
BEGIN
  EXECUTE format($_$SELECT EXISTS(SELECT * FROM %I WHERE COALESCE(trim(%I), '') <> '')$_$, 
                 table_name, column_name)
    INTO _found;
  RETURN NOT _found;
END;
$function$;

your example has more issues:

  • don't use count where you really need to know number of rows (items). This can be pretty slow on bigger tables
  • Usually for keywords are used uppercase chars
  • don't use variable names that are SQL, PL/pgSQL keywords (reserved or unreserved), there can be some problems in some contexts (count, user, ...)
  • this is classic example of some chaos in data - you should to disallow empty strings in data. Then you can use index and the predicate COLNAME IS NOT NULL. It can be pretty fast.
Pavel Stehule0
Related