I have multiple objects in an array . Now I have to insert all data with single query from nodeJS to postgreSQL

Question

I have received the data from frontend. So all the data is in req.body. First I map it then I am trying to insert it. But gives the error.

  router.post('/addItems', (req, res) => {

        let purchase_id = '';
        let item_code = '';
        let item_name = '';
        let description = '';
        let category_id = '';
        let location_id = '';
        let invoice_no = '';
        let warrantyend_Date = '';
        let created_by = '';
        let item_status = '';
        let complain_id = '';

        console.log(req.body);

        req.body.map((results)=>{

             this.purchase_id = results.purchase_id;
             this.item_code = results.item_code;
             this.item_name = results.item_name;
             this.description = results.description;
             this.category_id = results.category_id;
             this.location_id = results.location_id;
             this.invoice_no = results.invoice_no;
             this.warrantyend_Date = results.warrantyend_Date;
             this.created_by = results.created_by
             this.item_status = results.item_status;
             this.complain_id = results.complain_id;
      
       console.log('results----',results);
    })

If possible then also gave me idea, how to wrote procedure for it in postgreSQL.

Answer 1

you can change the input type to jsonb[] and modify the function accordingly. Here's the updated PostgreSQL function:

CREATE OR REPLACE FUNCTION add_items(items jsonb[])
RETURNS VOID AS $$
DECLARE
  item jsonb;
BEGIN
  FOREACH item IN ARRAY items
  LOOP
    INSERT INTO your_table_name (
      purchase_id, item_code, item_name, description,
      category_id, location_id, invoice_no, warrantyend_Date,
      created_by, item_status, complain_id
    ) VALUES (
      (item->>'purchase_id')::integer,
      item->>'item_code',
      item->>'item_name',
      item->>'description',
      (item->>'category_id')::integer,
      (item->>'location_id')::integer,
      item->>'invoice_no',
      item->>'warrantyend_Date',
      item->>'created_by',
      item->>'item_status',
      (item->>'complain_id')::integer
    );
  END LOOP;
END;
$$ LANGUAGE plpgsql;

Now, to call this function from Node.js, you should first convert each object in the req.body array to a JSON string and then pass the modified array as an argument to the function:

router.post('/addItems', async (req, res) => {
  try {
    // Convert each object in the req.body array to a JSON string
    const jsonItems = req.body.map(item => JSON.stringify(item));

    // Call the add_items function with the modified array
    const query = `SELECT add_items($1::jsonb[])`;
    await pool.query(query, [jsonItems]);

    res.status(200).send('Items inserted successfully');
  } catch (error) {
    console.error('Error inserting items:', error);
    res.status(500).send('Error inserting items');
  }
});