PostgreSQL中如何用宏/元编程简化重复查询函数调用？

假设存在对应示例数据，现有两个逻辑高度相似的PostgreSQL SQL函数，仅查询的目标列不同（通用场景下可能涉及不同表）：

CREATE OR REPLACE FUNCTION example.markout_666_example_666_price_table_666_price(_symbol text, _time_of timestamptz, _start interval, _duration interval)
  RETURNS float8
  LANGUAGE sql STABLE STRICT PARALLEL SAFE AS  -- !
$func$
SELECT p.price
FROM   example.price_table p
WHERE  p.symbol = _symbol
AND    p.time_of >= _time_of + _start
AND    p.time_of <= _time_of + _start + _duration
ORDER  BY p.time_of
LIMIT  1;
$func$;

CREATE OR REPLACE FUNCTION example.markout_666_example_666_price_table_666_volume(_symbol text, _time_of timestamptz, _start interval, _duration interval)
  RETURNS float8
  LANGUAGE sql STABLE STRICT PARALLEL SAFE AS  -- !
$func$
SELECT p.volume
FROM   example.price_table p
WHERE  p.symbol = _symbol
AND    p.time_of >= _time_of + _start
AND    p.time_of <= _time_of + _start + _duration
ORDER  BY p.time_of
LIMIT  1;
$func$;

调用时需要编写重复度极高的冗长语句：

SELECT symbol, time_of, example.markout_666_example_666_price_table_666_price(symbol, time_of, '3 hours', '24 hours') as markout_price,
                        example.markout_666_example_666_price_table_666_price(symbol, time_of, '25 hours', '24 hours') as markout_price_2,
                        example.markout_666_example_666_price_table_666_volume(symbol, time_of, '3 hours', '24 hours') as markout_volume
from example.interesting_times it; 

希望改用更简洁的写法，通过一个类似宏/元编程的example.markout结构实现相同效果：

SELECT symbol, time_of, example.markout('example.price_table', 'price', '3 hours', '24 hours') as markout_price,
                        example.markout('example.price_table', 'price', '25 hours', '24 hours') as markout_price_2,
                        example.markout('example.price_table', 'volume', '3 hours', '24 hours') as markout_volume
from example.interesting_times it; 

询问PostgreSQL是否有类似元编程的技术实现该需求，目前仅了解到Oracle的sql_macro以及PostgreSQL旧版本已废弃的宏命令。

PostgreSQL没有Oracle sql_macro那样原生的SQL宏功能，但可以通过以下两种方式实现需求：

1. 动态SQL函数（PL/pgSQL） #

创建一个通用的PL/pgSQL函数，接收表名、列名及时间参数，通过动态SQL拼接查询逻辑，同时注意SQL注入防护（使用format()函数的%I占位符处理标识符）：

CREATE OR REPLACE FUNCTION example.markout(_table regclass, _column text, _symbol text, _time_of timestamptz, _start interval, _duration interval)
  RETURNS float8
  LANGUAGE plpgsql STABLE STRICT PARALLEL SAFE
AS $func$
DECLARE
  result float8;
BEGIN
  EXECUTE format(
    'SELECT p.%I
     FROM   %s p
     WHERE  p.symbol = $1
     AND    p.time_of >= $2 + $3
     AND    p.time_of <= $2 + $3 + $4
     ORDER  BY p.time_of
     LIMIT  1',
    _column, _table
  ) INTO result
  USING _symbol, _time_of, _start, _duration;
  
  RETURN result;
END;
$func$;

调用方式调整为（需传入当前行的symbol和time_of参数）：

SELECT symbol, time_of, 
       example.markout('example.price_table', 'price', symbol, time_of, '3 hours', '24 hours') as markout_price,
       example.markout('example.price_table', 'price', symbol, time_of, '25 hours', '24 hours') as markout_price_2,
       example.markout('example.price_table', 'volume', symbol, time_of, '3 hours', '24 hours') as markout_volume
FROM example.interesting_times it;

关键注意点 #

• 使用regclass类型接收表名，自动处理模式名和引号，避免SQL注入并兼容带特殊字符的表名
• %I占位符会自动给列名添加双引号，处理列名包含特殊字符或关键字的情况
• 动态SQL会牺牲部分查询计划缓存，但对于这类简单查询影响极小

2. 利用LATERAL JOIN简化重复调用（无动态SQL） #

如果不想使用动态SQL，可以通过LATERAL JOIN预计算时间范围，再统一查询所需列，避免重复编写函数调用：

SELECT it.symbol, it.time_of,
       p1.price AS markout_price,
       p2.price AS markout_price_2,
       p1.volume AS markout_volume
FROM example.interesting_times it
LEFT JOIN LATERAL (
  SELECT price, volume
  FROM example.price_table p
  WHERE p.symbol = it.symbol
    AND p.time_of >= it.time_of + '3 hours'::interval
    AND p.time_of <= it.time_of + '3 hours'::interval + '24 hours'::interval
  ORDER BY p.time_of
  LIMIT 1
) p1 ON true
LEFT JOIN LATERAL (
  SELECT price
  FROM example.price_table p
  WHERE p.symbol = it.symbol
    AND p.time_of >= it.time_of + '25 hours'::interval
    AND p.time_of <= it.time_of + '25 hours'::interval + '24 hours'::interval
  ORDER BY p.time_of
  LIMIT 1
) p2 ON true;

这种方式无需额外创建函数，适合查询逻辑固定、仅参数变化的场景。

PostgreSQL官方曾在旧版本（如PostgreSQL 9.1及之前）支持CREATE MACRO命令，但该功能已被废弃并移除。目前原生不支持SQL宏，但通过上述动态SQL或LATERAL JOIN的方式可以满足需求。

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