如何修正PostgreSQL函数语法，支持传入字符串或字符串列表类型的用户参数

• 参数类型定义不符合需求：将入参stocks设为单值char类型，既无法承载字符串列表入参，定长char的自动补空格机制还会导致实际值匹配失败
• 原生SQL引用语法错误：IN ({stocks})是应用层拼接占位符的写法，PostgreSQL原生SQL不支持在语句块内用花括号直接引用函数参数，属于无效语法
• 存在冗余语法符号：SELECT语句末尾附带一个未匹配的右括号，直接触发语法校验失败
• 返回值类型不合理：返回列使用定长char类型，输出结果会自动填充长度空格，和实际symbol格式存在偏差

核心实现逻辑：使用text类型数组作为入参类型，搭配PostgreSQL原生的数组匹配语法替换原IN列表的写法，同时兼容单个字符串、字符串列表两类入参场景。

正确的函数创建代码如下：

CREATE OR REPLACE FUNCTION dividend(stocks text[])
RETURNS TABLE(
    symbol text,
    next_dividend_date date,
    ex_dividend_date date
) 
AS $$
    SELECT DISTINCT ON (security_stats.symbol) 
           security_stats.symbol,
           security_stats.next_dividend_date, 
           security_stats.ex_dividend_date
    FROM api.security_stats 
    WHERE security_stats.symbol = ANY(stocks)
    ORDER BY security_stats.symbol, security_stats.date DESC;
$$ LANGUAGE SQL;

调用方式说明 #

两类入参场景都可直接调用，无需额外适配：

• 传入单个股票代码：传入单元素数组即可，示例：SELECT * FROM dividend(ARRAY['AAPL']);
• 传入多个股票代码的列表：传入多元素数组即可，示例：SELECT * FROM dividend(ARRAY['AAPL', 'MSFT', 'GOOG']);

方案适配性说明 #

• 数组类型入参完全满足“既支持单字符串、也支持字符串列表”的要求，不需要额外做参数类型判断
• 用= ANY(stocks)替换原占位符IN写法，和原查询的过滤逻辑、执行效率完全一致，且该语法从PostgreSQL 8.2版本就开始支持，完全适配你当前使用的9.1版本
• 数组参数传递是PostgreSQL原生的参数绑定方式，不需要手动拼接SQL字符串，能彻底避免之前占位符拼接可能带来的SQL注入风险
• 替换原代码中冗余的右括号、将定长char调整为text类型，从根源上解决语法校验报错的问题

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