How to use pgx to return a NoRowsErr for a query that returns multiple rows

Getting Help
1

For my current project I’m using pgxpool to query the database using this select
query " query(ctx, SELECT * FROM invoices WHERE user_id = $1,id)".
This function returns a type pgx.Rows which holds the values of the query and an error type.
If an error exists, then value of the pgx.Rows isn’t returned.
However, this isn’t the case since the function pgx.Query() doesn’t returns an error for no rows.
In this case the function doesn’t return an error nor a value of type pg.Rows.
Only pgx.QueryRow() along with pgxscan.ScanOne() can return a NoRowsError for you.
The problem is pgx.ScanOne() only returns one row where I want to return multiple.

From what I gathered online I’m to supposed to use Rows.Next() to check if the next row exist otherwise it return false when there are no rows. However, when the query does return rows I’m not sure what I"m supposed to do.

My current implementation

func ReadInvoicesByUserID(id int) ([]*Invoice, fields.GrammarError) {
	ctx, db := bikeshop.Connect()
	defer db.Close()

	var tempInv Invoice
	var invoices []*Invoice
	_, fieldErr := ReadInvoices()

	if fieldErr.ErrMsgs != nil &&
		strings.Contains(fieldErr.ErrMsgs[0], "failed to connect to `user=username") {
		fmt.Printf("ReadInvoicesByUserID funct: error username doesn't exist")
		return nil, fieldErr
	}

	rows, err := db.Query(ctx, `SELECT * FROM invoices WHERE user_id = $1`, id)

	if !rows.Next() {
		// if no rows were found thats an NoRowsError
		if rows.Err() == nil {
			fmt.Println("Found an Error Iterating in Getting All the Invoices for the Specified User")
			fieldErr.AddMsg(fields.ResourceNotFound, "no rows in result set")
			return nil, fieldErr
		}
		return nil, fieldErr
	}

	// err := pgxscan.ScanAll(&invs, rows)

	// var err error
	for rows.Next() {
		fmt.Println("Processing Query in ReadInvoicesbyUserID")
		err = pgxscan.ScanOne(&tempInv, rows)
		if errors.Is(err, pgx.ErrNoRows) {
			fmt.Println("Found an Error Iterating in Getting All the Invoices for the Specified User")
			break
		}
		invoices = append(invoices, &tempInv)
	}

	if rows.Err() != nil {
		fmt.Println("Found an Error Iterating in Getting All the Invoices for the Specified User")
	}

	return invoices, fieldErr
}

refs
pgx_v5
pgxscan
pg_v5 pgxpool
pgx_v5 implementation for rows

2

This behavior is not unique to this package - any SQL database is usually implemented like this. If you ask “give me one row” it will be an error, if there is not row.
But SELECT * ... is asking “give me any rows that you find”. In most cases you don’t know how many rows to expect and zero rows is a valid result. - In most cases you can just proceed normally and handle such condition further up the chain. In a web application your frontend would just receive an empty list as the result for all invoices and could display “no invoices found”

If you really want to throw an error at this stage, the simples is probably after the for-loop, just add a line if len(invoices) == 0 { return nil, fmt.Errorf(...) }

1 Like