PostgreSQL 自定义函数传入字符串数组 遍历查询返回各股票最新行情

第一版plpgsql代码的错误点 #

• INSERT INTO ... VALUES(SELECT ...) 语法不兼容，PostgreSQL中INSERT接SELECT语句不需要套VALUES关键字
• 临时表后的返回查询引用了未定义的ArrayTextData别名，字段名和临时表实际列完全不匹配
• 未处理临时表重复创建的问题，多次调用函数时会因为ArrayData临时表已存在直接报错，且临时表默认是会话级，不会在函数执行结束后自动销毁，会占用多余存储
• 手动遍历计数器、临时表中转的写法完全冗余，既无必要也会大幅降低执行性能

第二版SQL函数的错误点 #

• 全局ORDER BY timestamp DESC LIMIT 10是取全表符合条件的前10条全局最新记录，不会按股票分组计算排名，因此时间较早的股票会直接被截断遗漏，无法拿到每个股票对应的最新单条记录。

核心逻辑是通过窗口函数对每个股票独立分组排名，仅取每个股票组内时间最新的1条记录，天然适配数组传入的过滤条件，不会遗漏任何传入的股票代码。

CREATE OR REPLACE FUNCTION get_latest_quotes(ArrayText character varying[])
RETURNS TABLE (ticker VARCHAR(10), "timestamp" int8, price float8) AS
$BODY$
  SELECT ticker, "timestamp", price
  FROM (
    SELECT 
      ticker, 
      "timestamp", 
      price,
      -- 按股票代码分组，组内按时间倒序排名
      ROW_NUMBER() OVER (PARTITION BY ticker ORDER BY "timestamp" DESC) AS row_rank
    FROM base.quotes
    -- 仅查询传入数组范围内的股票
    WHERE ticker = ANY(ArrayText)
  ) filtered_data
  -- 仅保留每个股票排名第一的最新记录
  WHERE row_rank = 1;
$BODY$ LANGUAGE sql STABLE;

性能优化提示 #

给业务表base.quotes添加联合索引，可避免全表扫描+内存排序，大幅提升查询速度：

CREATE INDEX idx_quotes_ticker_time ON base.quotes (ticker, "timestamp" DESC);

如果必须使用plpgsql循环逻辑（该写法会随数组长度增加导致查询次数线性上涨，性能远低于窗口函数方案），可按以下方式修正语法：

CREATE OR REPLACE FUNCTION get_latest_quotes(ArrayText character varying[])
RETURNS TABLE (ticker VARCHAR(10), quotetime text, price text) AS
$BODY$
DECLARE
  loop_ticker VARCHAR(10);
BEGIN
  -- 直接遍历数组元素，无需手动计算数组上下界、维护计数器
  FOREACH loop_ticker IN ARRAY ArrayText
  LOOP
    -- 单条查询对应股票的最新行情直接返回
    RETURN QUERY
    SELECT q.ticker, q.quotetime, q.price
    FROM quotes q
    WHERE q.ticker = loop_ticker
    ORDER BY q.quotetime DESC
    LIMIT 1;
  END LOOP;
  RETURN;
END;
$BODY$ LANGUAGE plpgsql VOLATILE;

注意：若传入数组长度较大，上述循环写法的性能会比纯SQL窗口函数方案差一个数量级，除非有特殊逻辑依赖逐次遍历，否则优先使用纯SQL实现。

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