Pass Integer Array as additionalScope to a pSQL Query

Queries and Resources
1

  • Goal: I try to insert values into a table with 7 columns. I populate them using the pSQL unnest function.

  • Steps:
    How i pass the additional Scope:

save_room_param.trigger(
  {additionalScope:{
    parameterid: roomSQLData[0],
    reportid: roomSQLData[1],
    optionid: roomSQLData[2],
    size: roomSQLData[3],
    measure: roomSQLData[4],
    comment: roomSQLData[5],
    room: roomSQLData[6]
  }}
)

My query:

INSERT INTO report_parameter (parameter_id, report_id, option_id, size, measure, comment, room)
VALUES  (
  unnest(array[{{parameterid}}]::int[]),
  unnest(array[{{reportid}}]::int[]),
  unnest(array[{{optionid}}]::int[]),
  unnest(array[{{size}}]::int[]),
  unnest(array[{{measure}}::text[]]),
  unnest(array[{{comment}}::text[]]),
  unnest(array[{{room}}]::int[])
  )

When i have a look at the Error Message the passing seems to be fine and the datatype is a integer array. But somehow within the SQL Prepared Statement it will be treated as text array with no possitiblity for conversion.

Does anybode has an idea how to solve that?

2

how you tried disabling SQL Prepared Statement? This might not be what you want (and might not fix anything), but I'd suggest trying it to see if the results differ. You can find this under the Resource Settings:

image
image1010×355 15.8 KB

1 Like
3

Thanks for you proposal, however i found the solution:
change it from unnest(array[{{parameterid}}]::int[]), to the following: unnest(array[{{parameterid}}::integer[]]),

4

ahhhhh, tricky. out of curiosity, do you happen to know if ::numberic[] would have worked?

5

Ah maybe that was missleading i think the important change was to move the ::int into the SQL Array Declaration. Before i casted the SQL array to ::int but now i cast the parameter before the SQL Cast to an integer

1 Like