如何在Supabase中创建通用动态RPC函数获取列唯一值及关联查询？

一、单表多列通用唯一值查询RPC函数 #

需求：创建可复用的RPC函数，传入表名和列名即可返回该列的唯一值列表，替代以下硬编码的单表SQL：

SELECT owner
FROM customers 
GROUP BY owner 
ORDER BY owner Asc;

实现方案 #

使用PostgreSQL的PL/pgSQL编写动态SQL，通过format()函数处理表名、列名这类标识符参数，既避免语法错误，也能防范SQL注入风险。

函数代码：

CREATE OR REPLACE FUNCTION fetch_distinct_values(
  table_name text,
  column_name text
)
RETURNS TABLE (value text) AS 
$BODY$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT DISTINCT %I::text FROM %I GROUP BY %I ORDER BY %I ASC;',
    column_name, table_name, column_name, column_name
  );
END;
$BODY$
LANGUAGE plpgsql;

关键说明 #

• %I是format函数的标识符占位符，会自动处理含特殊字符或关键字的表/列名转义
• 用DISTINCT替代GROUP BY实现唯一值查询，逻辑等价且更简洁
• 将结果统一转为text类型，适配不同数据类型的列场景

二、关联查询场景的通用RPC函数 #

问题说明 #

编写关联查询的通用RPC函数时，出现错误'column "customers.owner_id" does not exist'，硬编码版本可正常运行，但参数化版本因字段引用歧义导致报错。

错误代码示例 #

CREATE OR REPLACE FUNCTION fetch_autocomplete_options(
  table_name text,
  join_table_name text,
  join_column_name text,
  table_column_name text,
  join_table_column_name text
)
RETURNS TABLE ( value text) AS 
$BODY$
  BEGIN
      RETURN QUERY EXECUTE format(
        'SELECT DISTINCT %I::text FROM %I INNER JOIN %I ON %I=%I ORDER BY %I ASC;',
        join_column_name, table_name, join_table_name, table_column_name, join_table_column_name, join_column_name
      );
  END;
$BODY$
LANGUAGE plpgsql;

修正后的正确代码 #

错误根源是未给表指定别名，导致数据库无法区分不同表中的同名字段。修正后为表添加别名，并通过别名明确引用列：

CREATE OR REPLACE FUNCTION fetch_autocomplete_options(
  table_name text,
  join_table_name text,
  join_column_name text,
  table_column_name text,
  join_table_column_name text
)
RETURNS TABLE ( value text) AS 
$BODY$
  BEGIN
      RETURN QUERY EXECUTE format(
        'SELECT DISTINCT j.%I::text FROM %I as t INNER JOIN %I as j ON t.%I::text = j.%I::text ORDER BY j.%I::text ASC;',
        join_column_name, table_name, join_table_name, table_column_name, join_table_column_name, join_column_name
      );
  END;
$BODY$
LANGUAGE plpgsql;

核心修正点 #

• 为主表和关联表分别添加别名t和j
• 通过别名.%I的方式明确列所属的表，消除字段歧义
• 关联条件中同样使用别名引用列，确保数据库能正确识别字段来源

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