Hi Retool team!
I'm using Retool to insert multiple records into two tables (the main table + its bridge table) in Postgres at the same time. Two tables are dependent: when a new record enters the main table and gets its uuid, a new record should also be inserted into the bridge table with its foreign key column referring to the main_table_uuid. I don't want either insert to fail. Either data are entered into both tables or none get inserted.
I've found this post solution: "Using a Postgres insert statement, which returns the id value into another insert statement" applicable.
But in my case, the data to insert are outputted from a javascript and the number of rows to insert is variable. I don't know 1) how to refer to the javascript output in a SQL statement 2) how to structure the insert statement in SQL if the number of rows is variable
Below is the workflow I'm thinking of
WITH input_data AS (
SELECT * FROM VALUES({{ javascript.data }})
),
first_insert AS (
INSERT INTO table_1 (field1, field2, field3)
VALUES( SELECT * FROM input_data)
RETURNING id
)
INSERT INTO another_table (first_id, field_4)
SELECT id as first_id, 'some_value' as field_4 FROM first_insert
Any suggestion is appreciated. Thank you!