如何在PL/pgSQL函数中使用引号并扩展WHERE子句检测空值/空字符串

1. PL/pgSQL函数中引号的使用方法 #

在PL/pgSQL里处理引号分几种场景，对应不同的解决方式：

• 函数体定义用美元引号（$标记$）
  这是最常用的方式，能避免函数体内单引号的转义麻烦。定义函数时，用$function$（或自定义标记如$myfunc$）包裹函数体，内部直接使用单引号即可，无需转义。
  示例：

  CREATE OR REPLACE FUNCTION test_quote() RETURNS void AS $function$
  BEGIN
      RAISE NOTICE 'This is a string with a single quote: ''test''';
  END;
  $function$ LANGUAGE plpgsql;
  

• 单引号的转义（非美元引号场景）
  如果必须在普通字符串中使用单引号，需要把单个单引号写成两个连续的单引号（''）来转义。
  示例：

  -- 不用美元引号的函数体写法
  CREATE OR REPLACE FUNCTION test_escape() RETURNS void AS '
  BEGIN
      RAISE NOTICE ''This is a string with a single quote: ''''test'''''';
  END;
  ' LANGUAGE plpgsql;
  

• 动态SQL中的引号处理
  动态拼接SQL时，要注意标识符（表名、列名）和字符串值的引号处理，避免SQL注入和语法错误：

  • 用quote_ident()函数处理标识符，或在FORMAT()中用%I占位符；
  • 用quote_literal()函数处理字符串值，或在FORMAT()中用%L占位符。
    示例：

  DECLARE
      tab_name varchar := 'users';
      col_name varchar := 'username';
      user_val varchar := 'Alice O''Neil';
  BEGIN
      -- 方式1：用quote_ident和quote_literal
      EXECUTE 'SELECT * FROM ' || quote_ident(tab_name) || ' WHERE ' || quote_ident(col_name) || ' = ' || quote_literal(user_val);
      
      -- 方式2：用FORMAT更简洁
      EXECUTE FORMAT('SELECT * FROM %I WHERE %I = %L', tab_name, col_name, user_val);
  END;
  

2. 修改public.is_column_empty函数的实现 #

原函数仅判断列是否全为NULL，现在需要扩展为判断所有行的列值要么是NULL，要么去除首尾空格后为空字符串。修改思路是调整WHERE子句，统计不符合条件的行（即trim后不为空的行），如果统计结果为0，说明所有行都满足要求。

修改后的函数代码 #

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
    -- 用FORMAT的%I处理标识符，避免SQL注入和语法错误
    EXECUTE FORMAT('SELECT COUNT(*) FROM %I WHERE coalesce(TRIM(%I), '''') <> ''''', table_name, column_name) INTO count;
    RETURN (count = 0);
END;
$function$;

关键修改说明 #

• 原WHERE %s IS NOT NULL替换为WHERE coalesce(TRIM(%I), '') <> ''：
  • TRIM(%I)对列值去除首尾空格；
  • coalesce(..., '')将NULL值转换为空字符串；
  • <> ''筛选出trim后不为空的行（即不符合“NULL或trim后为空”的行）；
• 使用%I代替原有的%s，自动完成标识符的转义处理，比手动调用quote_ident()更简洁；
• 函数逻辑保持不变：如果统计到的不符合条件的行数为0，返回true，表示列中所有行都满足NULL或trim后为空。

内容的提问来源于stack exchange，提问作者kitchenprinzessin