如何利用美元引号实现带日期参数的查询字符串拼接？

美元引号包裹的内容属于字符串常量，PL/pgSQL不会自动替换其中的变量名，需要通过字符串拼接的方式将参数值插入到美元引号的片段中，具体有两种实现方式：

方式一：直接拼接美元引号片段 #

通过||操作符将变量值与美元引号包裹的SQL片段拼接，不需要转义单引号，比传统单引号拼接更简洁：

p_ReqStr_new := $$
with prm as
(
  select
    1::int4 as id,
    to_timestamp('$$ || to_char(p_BegRepDate, 'DD.MM.YYYY') || $$','DD.MM.YYYY')::timestamp as p_BegDate,
    to_timestamp('$$ || to_char(p_EndRepDate, 'DD.MM.YYYY') || $$','DD.MM.YYYY')::timestamp as p_EndDate,
    $$ || cast(p_IsIncludeTestLpu as varchar(1)) || $$::int8 as p_IsIncludeTestLpu
)$$;

执行后得到的预期结果：

with prm as
(
  select
    1::int4 as id,
    to_timestamp('01.01.2023','DD.MM.YYYY')::timestamp as p_BegDate,
    to_timestamp('31.01.2023','DD.MM.YYYY')::timestamp as p_EndDate,
    2::int8 as p_IsIncludeTestLpu
)

方式二：结合format()函数（推荐） #

使用format()函数配合美元引号，通过占位符%s替换参数值，代码可读性更强，也能避免拼接错误：

p_ReqStr_new := format($$
with prm as
(
  select
    1::int4 as id,
    to_timestamp('%s','DD.MM.YYYY')::timestamp as p_BegDate,
    to_timestamp('%s','DD.MM.YYYY')::timestamp as p_EndDate,
    %s::int8 as p_IsIncludeTestLpu
)$$, 
to_char(p_BegRepDate, 'DD.MM.YYYY'), 
to_char(p_EndRepDate, 'DD.MM.YYYY'), 
cast(p_IsIncludeTestLpu as varchar(1))
);

这种方式会自动将参数值填入对应的占位符位置，生成的结果和方式一完全一致，但代码结构更清晰，维护成本更低。

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