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