Batch insert into Retool through javascript trigger

Queries and Resources
,
1

Hey, I'm currently trying to pass Postgres "tuples" within additionalScope in order to be able to make a batch insert that gets triggered by a javascript function.

What I need to do is to prepare some data that needs to get

  • stored in retool database
  • return the newly created entrys with more information from a join
  • sync these new items

I though I could just create an array of objects by looping through the data, then create the "tuples" that you can normally use in postgres.

For what it seems however, you can only pass key-value pair and I'm not sure if there is a way or what the best way to still achieve what I want.

{{ tuples }} (should) look like this

('76ced534-2dc5-48e5-8af1-62f2b2daa122', '2024-05-21T12:00:00.000Z', '2024-05-21T13:00:00.000Z', 20, 0, 'active'), 
('76ced534-2dc5-48e5-8af1-62f2b2daa122', '2024-05-22T12:00:00.000Z', '2024-05-22T13:00:00.000Z', 20, 0, 'active'),

and my postgres query

WITH inserted_dates AS (
    INSERT INTO booking_tool_api_dates (
        "id", 
        "start", 
        "end", 
        "maxCapacity", 
        "usedCapacity", 
        "status",
    )
    VALUES 
    {{ tuples }}
    RETURNING *
)
SELECT 
    d.*, 
    a.*, 
    d.id AS date_id, 
    a.id AS activity_id
FROM 
    inserted_dates d
LEFT JOIN 
    booking_tool_api_activity a
ON 
    d.activity = a.id;

Is there a way to pass these values correctly? The problem is, I need to be able to do some joins with the data I've just inserted and return the values so that I can use these items to sync them somewhere else.

Not sure how I should get the ids of the newly created items otherwise so I could make some further calls.

Thanks in advance

2

Maybe...what does tuples actually look like? Seems like you might need to transform it to pass it in the right format for the INSERT. As a test, can you get the INSERT to work without the rest? Put another way, if your query was just

INSERT INTO booking_tool_api_dates (
  "id", 
  "start", 
  "end", 
  "maxCapacity", 
  "usedCapacity", 
  "status",
)
VALUES 
  {{ tuples }}

does that work?

3

Unfortunately not :frowning:

I found this: AdditionalScope Problem and for what it seems you cant just use a variable and pass whatever you want, it has to be some sort of value and it seems, that this

('76ced534-2dc5-48e5-8af1-62f2b2daa122', '2024-05-21T12:00:00.000Z', '2024-05-21T13:00:00.000Z', 20, 0, 'active'), 
('76ced534-2dc5-48e5-8af1-62f2b2daa122', '2024-05-22T12:00:00.000Z', '2024-05-22T13:00:00.000Z', 20, 0, 'active'),

does not count as a "value"