Question 114594 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to include quote in plpgsql function

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$
;
Tags:

2

Answers


  1. 0

    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?

    Login or Signup to reply.
  2. 0

    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.
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search