Bulk insert records - Pure SQL statement to get IDs of generated records

Queries and Resources
1

Hi - I face a (trivial?) problem, but I could not fix it so far and in the forum I could not find a post. Here is my problem:

In a code workflow I create an object with records I want to use in a following query block. Using GUI this works perfectly with Bulk insert records referring to the object {{codeblock.records.data}}.

However, I want use SQL to achieve that, adding the command returning id to get the ids of the created records directly from the database.

Unfortunately I could not figure out how to access the keys/columns and the values of the object {{codeblock.record.data} in an INSERT INTO statement.

Tried almost everything, changed the code block so that it returns columns and values in separate arrays and then try to unnest it in the SQL statement. But Retool always returns a syntax error.

Any idea what I'm doing wrong?

2

Just create a SQL json statement
Select "your-id-column-name"
From {{formatAsArray(code-block.data)}}

Have you tried this?

3

Thank you! This doesn't work unfortunately. Even if I run a pure (not INSERT) SQL statement like "SELECT * FROM {{formatAsArray(codeblock.data)}}. Looks like Postgres cannot handle arrays. I tried to unnest it but "Function" unknown.

This seems to be tricky. Any other idea?

4

Don't do it in Postgres.

Do it in "Query JSON with SQL" in Retool. That should give you the list of IDs.
Then from there, you can do an INSERT with your JSON query record rows.

There's probably a slick way to do this with a map/filter function from a javascript ternary. Ask ChatGPT with your specific use case, and say it's in Retool.