String concatenation with dollar-quoting

Question

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?

Answer 1

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.

Answer 2

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:

• Insert text with single quotes in PostgreSQL
• How to concatenate columns in a Postgres SELECT?
• Parameter concatenation in dynamic SQL
• PostgreSQL - Writing dynamic sql in stored procedure that returns a result set