英文:
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:
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论