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?

Is this for dynamic SQL in a PL/pgSQL function? If so, you can probably have it a lot simpler, cheaper and safer. What data types are the input variables? Can you disclose a complete (minimal) function and your version of Postgres? — Erwin Brandstetter, Aug 09 '23 at 01:31

score 2 · Answer 1 · answered Aug 09 '23 at 00:47

2

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.

answered Aug 09 '23 at 00:47

jjanes

37,812
5
27
34

Thank you for the quick answer. I have refresh my knowledge on quoting and concatenation. Yes, it works! I have replaced all single quotes with ```$$``` – Ivan Vodopyanov Aug 09 '23 at 23:53

score 2 · Answer 2 · answered Aug 09 '23 at 01:57

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

Thank you, Erwin, for the example of ```USING``` keyword. In my case, I re-create a dynamic SQL and pass in into ```dblink()``` function. I guess I cannot use ```EXECUTE``` for this purpose. — Ivan Vodopyanov, Aug 09 '23 at 23:56
@Ivan: You can `EXECUTE` the whole call, but you cannot pass *values* into the remote query string this way. Even so, your code can probably be simplified a bit. You'd start a new question with the necessary details for this ... — Erwin Brandstetter, Aug 10 '23 at 00:07

String concatenation with dollar-quoting

2 Answers2