How to run a delete query in golang that returns the id of deleted entity-CodePudding

Query :

DELETE FROM *tableName* RETURNING *id*

How to run this query in my golang code? I tried

resp, err := pgConnPool.Exec(context.Background(), deleteQuery)

but it gives pgConn.CommandTag(can be used to get rowsCount but not actual rows)

CodePudding user response：

The query is OK (just ensure it ends with RETURNING *id*).

Assuming you are only deleting one entity, you can use QueryRow as suggested by @mkopriva

package yourpackage

import (
  "context"

  "github.com/google/uuid"
  "github.com/jackc/pgx/v4"
)

func YourMethod() {
  // ...

  var recordID uuid.UUID

  err := pgConnPool.QueryRow(context.Background(), deleteQuery).Scan(&recordID)
  if err != nil {
    // handle error.
  }

  // recordID now contains the id of the deleted record.
}

Or wrapped in a handler

package yourpackage

import (
  "context"

  "github.com/google/uuid"
  "github.com/jackc/pgx/v4"
)

func DeleteRecord(/*your args*/) (uuid.UUID, error) {
  var recordID uuid.UUID

  // build your deleteQuery object.

  err := pgConnPool.QueryRow(context.Background(), deleteQuery).Scan(&recordID)

  return recordID, err
}

If you want to add the possibility to delete multiple records at once

package yourpackage

import (
  "context"

  "github.com/google/uuid"
  "github.com/jackc/pgx/v4"
)

func DeleteRecords(/*your args*/) ([]uuid.UUID, error) {
  var recordIDs []uuid.UUID

  // build your deleteQuery object.

  rows, err := pgConnPool.Query(context.Background(), deleteQuery)
  if err != nil {
    return nil, err
  }

  defer rows.Close()

  for rows.Next() {
    var recordID uuid.UUID

    if err := rows.Scan(&recordID); err != nil {
      return nil, err
    }

    recordIDs = append(recordIDs, recordID)
  }

  return recordIDs, rows.Err() // Ensure no reading error occurred  
}