Error passing parameters into crosstab query

[philipm-domuso]

I think this may be a more advanced case, or a PG parametrized query issue, but I wanted to see if you have any advice on how to approach this.

Code:

const ids = [1,2];
await sql`select *
                from public.crosstab($$
SELECT
  day_of_month,
  months_ago,
  sum(amount) as day_amount
FROM payments
WHERE
  pay_id IN ${sql(ids)}
GROUP BY date_day, day_of_month
ORDER BY 1,2$$,
$$VALUES('months_ago_0'::text), ('months_ago_1'::text), ('months_ago_2'::text) $$
) as ct ("date_of_month" int, "months_ago_0" numeric, "months_ago_1" numeric, "months_ago_2" numeric)`;

The error is could not determine data type of parameter $1. Logging with {debug: console.log} produces the following PG query:

PostgresError: could not determine data type of parameter $1
    at ErrorResponse ...
    at node:internal/main/run_main_module:23:47 {
  query: '\n' +
    'select *\n' +
    '                from public.crosstab($$\n' +
    'SELECT\n' +
    '  day_of_month,\n' +
    "  months_ago,\n" +
    '  sum(amount) as day_amount\n' +
    'FROM payments\n' +
    'WHERE\n' +
    '  pay_id IN ($1,$2)\n' +
    'GROUP BY date_day, day_of_month\n' +
    'ORDER BY 1,2$$,\n' +
    "$$VALUES('months_ago_0'::text), ('months_ago_1'::text), ('months_ago_2'::text) $$\n" +
    ') as ct ("date_of_month" int, "months_ago_0" numeric, "months_ago_1" numeric, "months_ago_2" numeric)',
  parameters: [ 1, 2 ],
  args: [ Builder { first: [ 1, 2 ], rest: [] } ],
  types: [ 0, 0 ]
}

Is sql.unsafe my only option for this case?

[porsager]

You can't add postgres protocol level parameters inside the crosstab string, since it's just a string.

What you can do is either use sql.unsafe inside the parameter, or maybe you can use format like this:

const ids = [1,2]

await sql`
  select 
    * 
  from public.crosstab(
    format(
      $$
        select
          day_of_month,
          months_ago,
          sum(amount) as day_amount
        from payments
        where
          pay_id = any(%L::text[])
        group by date_day, day_of_month
        order by 1,2
      $$, 
      ${ '{' + ids.join(',') + '}' }
    ),
    $$ values('months_ago_0'), ('months_ago_1'), ('months_ago_2') $$
  ) as ct (
    date_of_month int, 
    months_ago_0 numeric, 
    months_ago_1 numeric, 
    months_ago_2 numeric
  )
`