How to insert multiple records into two tables

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 (

first_insert AS (
  INSERT INTO table_1 (field1, field2, field3)
  VALUES( SELECT * FROM input_data)

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!

Hey @doris.l!

Great question, I believe you could use the same query from your example but pass the values from your JS query to this SQL query using additionalScope. (link to our docs on this here). With this setup, you could pass each set of values one at a time to this query, do you think this could work for your use case here?