How do I add multiple rows to table in PostgreSQL?

I’ve got a table

jobs_functions
job_id function_id
(int) (int)

Both fields are indexes. Primary key is the combination of this two fields.
I want to add rows to this table by clicking button one time.

For example:
I’ve got job_id, and a dropdown with function_id. I select in dropdown 2 function_id. Click button, two rows adding to table with similar job_id and different function_id.

How can I do this?

You need to do a batch insert query and roll your own array to send back to the server. Here is a snippet from my Retool Patterns document I am compiling to help me remember how to do things. This example is a bulk update, but the process is the same for a bulk insert.

Creating an Array From Scratch

You may be doing a bulk insert on a table that you do not have a query or table for, a many-many linkage table for instance, and you need to get columns from multiple sources, use this pattern.

First you need a Temporary State var to hold the array between steps. bulkBundle in this example. It does not need a Default Value

Next you need to create a js query that will build the array which is used by the Bulk query. This example updates records to the line_items table and uses the current invoiceid as the foreigh key.

// jsMakeBulkBundleArray
  var newData = [];
  tblLineItems.selectedRow.data.forEach(row => {
    newData.push({
    line_item_id: row.line_item_id,
    bundled_sku: tblProducts.selectedRow.data[0].sku
  })
});
bulkBundle.setValue(newData);

Next up, make the actual sql bulk query. Use the temp state var as your source for the array.

Now make another js query that will tie it all together.

// jsBulkBundle
  jsMakeBulkBundleArray.trigger({
    onSuccess:function(data) {
    qryBulkBundle.trigger()
  }
});

Finally you need to trigger the jsBulkBundle query from somewhere like a save button.

4 Likes

Thank you! It works literally I want. I have never used temporary state yet. It was only thing I spend time to read in docs a little bit more. Everything else was pretty clear. Thank you one more time, you’ve been saved a lot of my time

2 Likes

Thanks Bradly! Really appreciate your thoughtful and detailed response!

1 Like

I improved this a bit.

In the make array query, just return the array rather than set the temp var.

// jsMakeBulkBundleArray
  var newData = [];
  tblLineItems.selectedRow.data.forEach(row => {
    newData.push({
    line_item_id: row.line_item_id,
    bundled_sku: tblProducts.selectedRow.data[0].sku
  })
});
return newData

In your main query use the returned value to pass it to the bulk query:

// jsBulkBundle
  jsMakeBulkBundleArray.trigger({
    onSuccess:function(data) {
      qryBulkBundle.trigger({
        additionalScope: {
          dataArray: data
        }
      })
  }
});

And then use {{dataArray}} as the Array of records to update in your SQL query.

25% fewer moving parts to keep track of.