CODEWITHSUNDEEP
sqlpostgresqlgosqlx
user1354934
user1354934

Reputation: 8851

How to create a transaction that inserts but then returns the inserted data

I am working on a REST API so I am trying to implement a way for users to create a new service ticket.

Everything is working fine except for when it comes to storing things in the db (postgres).

Here's a snippet of the transaction once it is generated:

BEGIN;

INSERT INTO service_request (id, ...)
VALUES (...);

INSERT INTO media (id, filename, ...)
VALUES (...),
(...),
(...);

INSERT INTO servicerequest_media(service_request_id, media_id)
values (..., ...),
(..., ...),
(...,...);

COMMIT;

Using sqlx prepared statements, I know that the result contains some metadata such as the last inserted id. However, how can I add a select query to my transaction and get the results of that query?

stmt, err := s.db.Prepare(CREATE_SERVICE_REQUEST)
if err != nil {
    //// 
}
res, err := stmt.Exec()
if err != nil {
    ////
}

Or, do I need to do a 2nd query to get the result?

I am pretty new to this, so please let me know if I need to give more context.

Upvotes: 1

Views: 382

Answers (1)

edkeveked
edkeveked

Reputation: 18401

Exec does not return the rows created or inserted. It only returns the last inserted element id.

If you would like to get the rows, you can consider using Query or QueryRow.

rows, err := stmt.Query()
if rows.Next {
    // declare variable of any type string, int or struct
    rows.Scan(&variable) // string, int
    // or
    rows.Scan(&variable.field1, &variable.field2) // where field1, field2 are the properties of your struct
    // do whatever you want with the variable
}

Upvotes: 1

Related Questions