Syntax error when trying execute dynamic query which contain dollar quoting to escape nested single quotes

Question

I have following piece of code, which does following

Generate set of string column names when provided a date range which are simply dates between the range
Then this function is used within an another function to generate dynamic query string
Next generated dynamic query string used with EXECUTE

As I notice syntax error occour at EXECUTE statement, I have already tried different variation of dollar quoting without success.

DROP EXTENSION IF EXISTS tablefunc;
CREATE EXTENSION IF NOT EXISTS tablefunc;


DROP FUNCTION IF EXISTS app_public.generate_date_columns(p_from_date DATE, p_to_date DATE);

-- Step 2: Create a function that generates the dynamic SQL query
CREATE OR REPLACE FUNCTION app_public.generate_date_columns(p_from_date DATE, p_to_date DATE)
  RETURNS TEXT AS $$
DECLARE
  column_names TEXT := '';
  pay_date DATE;
BEGIN
  -- Generate column names and select columns
  FOR pay_date IN SELECT generate_series(p_from_date, p_to_date, '1 day'::interval)::DATE LOOP
    column_names := column_names || format('"%s" INT,', pay_date);
  END LOOP;

  -- Remove the trailing commas
  column_names := substr(column_names, 1, length(column_names) - 1);

  RAISE NOTICE 'Columns: %s', column_names;

  RETURN column_names;
END
$$ LANGUAGE plpgsql;


SELECT app_public.generate_date_columns('2023-05-30'::DATE, '2023-06-05'::DATE);


DROP FUNCTION IF EXISTS app_public.incentive_per_line(DATE, DATE);

CREATE OR REPLACE FUNCTION app_public.incentive_per_line(p_from_date DATE, p_to_date DATE)
  RETURNS SETOF RECORD AS $$
DECLARE
  column_names TEXT;
  v_dynamic_query TEXT;
BEGIN

    SELECT app_public.generate_date_columns(p_from_date, p_to_date) INTO column_names;

    SELECT  FORMAT($tag$'SELECT * FROM crosstab($ct$'SELECT op.line_no::SMALLINT, pay.payout_date::DATE, pay.amount::INT AS incentive
    FROM app_public.operators AS op INNER JOIN app_public.payouts AS pay ON pay.oid = op.id
    WHERE pay.payout_date >= date($from$'%s'$from$) AND pay.payout_date <= date($to$'%s'$to$)'$ct$) AS ct(ln SMALLINT, $cn1$%s$cn1$)'$tag$, p_from_date, p_to_date, column_names)
    INTO v_dynamic_query;

    RAISE NOTICE '%s', v_dynamic_query;

   EXECUTE v_dynamic_query;

END
$$ LANGUAGE plpgsql;


select app_public.incentive_per_line('2023-05-30'::DATE, '2023-06-05'::DATE);

I want dynamic query to execute successfully without any syntax errors. Additionally appreciate If you help me to improve this dollar quoting experience.

Syntax Error:

[42601] ERROR: syntax error at or near "'SELECT * FROM crosstab($ct$'" Where: PL/pgSQL function app_public.incentive_per_line(date,date) line 16 at EXECUTE

Could you narrow it down to the part with the error? Could you tell us what the error is, exactly? — Nick Barnes, Jun 08 '23 at 04:50
Can anyone please explain why there are down votes?, down voting result in question not being exposed to interested audience. — user158, Jun 08 '23 at 05:41

score 0 · Answer 1 · answered Jun 08 '23 at 07:10

0

The error message shows the failing syntax, specifically, the single ' preceding SELECT.

[42601] ERROR: syntax error at or near "'SELECT * FROM crosstab($ct$'" Where: PL/pgSQL function app_public.incentive_per_line(date,date) line 16 at EXECUTE

The SELECT statement was already quoted by $ct$ , so the additional single ' is part of the string instead of quoting the string.

answered Jun 08 '23 at 07:10

JohnH

2,001
1
2
13

Thank you for the response, I removed `'` now I am getting another syntax error. when fixed there is an another and so on. This is lot of things to go wrong here: strings argument for where clause, column names with series of double quoted strings. – user158 Jun 08 '23 at 10:22

Syntax error when trying execute dynamic query which contain dollar quoting to escape nested single quotes

1 Answers1