使用美元引用符进行字符串连接

huangapple go评论74阅读模式
英文:

String concatenation with dollar-quoting

问题

使用连接符来准备带有参数值的查询字符串。

当我使用单引号时:

p_ReqStr_old := '
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
)

当我使用美元引用时:

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(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
)

有没有一种方法可以使用美元引用机制来获得带有日期值的结果?

英文:

I use concatenation to prepare a query string with parameter values

When I use single quotes:

p_ReqStr_old := '
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
)';

I receive the following result:

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
)

When I use dollar quoting:

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
)$$;

I receive this undesirable result:

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
)

Is there a way to receive a result with date values with dollar quoting mechanics?

答案1

得分: 2

当你切换到美元引用时,你也删除了连接操作符。如果你想保留连接操作符,那就保留它。这意味着你需要在||的两边都关闭和重新打开美元引用,就像你在使用单引号时所做的那样。切换引号只意味着你不再需要转义字面上的单引号,它不会改变连接操作的工作方式。

也许你应该使用FORMAT函数。

英文:

When you switched to dollar quoting, you also removed the concatenation. If you want to keep concatenation, then keep it. That does mean you would need to keep closing and reopening the dollar quotes on each side of the ||, just like you did when using the single quotes. Switching the quote mark just means you don't need to escape the literal single quote marks anymore, it doesn't change how concatenation works.

Maybe you should use the FORMAT function instead.

答案2

得分: 2

请参考jjanes关于对美元引用的误解的回答。

假设这是关于在PL/pgSQL代码块中使用EXECUTE的动态SQL,除了函数体的外部引号之外,您不需要任何美元引用,也不需要拼接。使用USING子句传递值更加便宜和安全:

示例:

DO
$do$
DECLARE
   p_BegRepDate timestamp := '2023-01-01';
   p_EndRepDate timestamp := '2023-01-31';
   p_IsIncludeTestLpu text := '234';        -- ??
BEGIN
   EXECUTE
   $q$  -- 没有嵌套的',可以直接使用
   WITH prm AS (
      SELECT 1::int4 AS id
           , $1 AS p_begdate
           , $2 AS p_enddate
           , $3 AS p_is_include_test_lpu
      )
   TABLE prm
   $q$
   USING p_BegRepDate, p_EndRepDate              -- 假设这些已经是timestamp类型
       , (p_IsIncludeTestLpu::varchar(1))::int8  -- 也可能可以简化
   ;
END
$do$;

相关链接:

英文:

See jjanes' answer about misunderstandings around dollar-quoting.

Assuming this is about dynamic SQL with EXECUTE in a PL/pgSQL code block, you do not need any dollar-quoting (except the outer quotes for the function body) and no concatenation, either. Passing values with the USING clause is cheaper and safer:

Demo:

DO
$do$
DECLARE
   p_BegRepDate timestamp := '2023-01-01';
   p_EndRepDate timestamp := '2023-01-31';
   p_IsIncludeTestLpu text := '234';        -- ??
BEGIN
   EXECUTE
   $q$  -- no nested ', could just be plane '
   WITH prm AS (
      SELECT 1::int4 AS id
           , $1 AS p_begdate
           , $2 AS p_enddate
           , $3 AS p_is_include_test_lpu
      )
   TABLE prm
   $q$
   USING p_BegRepDate, p_EndRepDate              -- assuming these are type timestamp already
       , (p_IsIncludeTestLpu::varchar(1))::int8  -- can probably be simplified, too
   ;
END
$do$;

Related:

huangapple
  • 本文由 发表于 2023年8月9日 08:29:24
  • 转载请务必保留本文链接:https://go.coder-hub.com/76863857.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定