Source file src/github.com/jackc/pgx/v5/rows_test.go

Documentation: github.com/jackc/pgx/v5

     1  package pgx_test
     2  
     3  import (
     4  	"context"
     5  	"errors"
     6  	"fmt"
     7  	"os"
     8  	"testing"
     9  	"time"
    10  
    11  	"github.com/stretchr/testify/assert"
    12  	"github.com/stretchr/testify/require"
    13  
    14  	"github.com/jackc/pgx/v5"
    15  	"github.com/jackc/pgx/v5/pgconn"
    16  	"github.com/jackc/pgx/v5/pgxtest"
    17  )
    18  
    19  type testRowScanner struct {
    20  	name string
    21  	age  int32
    22  }
    23  
    24  func (rs *testRowScanner) ScanRow(rows pgx.Rows) error {
    25  	return rows.Scan(&rs.name, &rs.age)
    26  }
    27  
    28  func TestRowScanner(t *testing.T) {
    29  	t.Parallel()
    30  
    31  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
    32  		var s testRowScanner
    33  		err := conn.QueryRow(ctx, "select 'Adam' as name, 72 as height").Scan(&s)
    34  		require.NoError(t, err)
    35  		require.Equal(t, "Adam", s.name)
    36  		require.Equal(t, int32(72), s.age)
    37  	})
    38  }
    39  
    40  type testErrRowScanner string
    41  
    42  func (ers *testErrRowScanner) ScanRow(rows pgx.Rows) error {
    43  	return errors.New(string(*ers))
    44  }
    45  
    46  // https://github.com/jackc/pgx/issues/1654
    47  func TestRowScannerErrorIsFatalToRows(t *testing.T) {
    48  	t.Parallel()
    49  
    50  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
    51  		s := testErrRowScanner("foo")
    52  		err := conn.QueryRow(ctx, "select 'Adam' as name, 72 as height").Scan(&s)
    53  		require.EqualError(t, err, "foo")
    54  	})
    55  }
    56  
    57  func TestForEachRow(t *testing.T) {
    58  	t.Parallel()
    59  
    60  	ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
    61  	defer cancel()
    62  
    63  	pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
    64  		var actualResults []any
    65  
    66  		rows, _ := conn.Query(
    67  			context.Background(),
    68  			"select n, n * 2 from generate_series(1, $1) n",
    69  			3,
    70  		)
    71  		var a, b int
    72  		ct, err := pgx.ForEachRow(rows, []any{&a, &b}, func() error {
    73  			actualResults = append(actualResults, []any{a, b})
    74  			return nil
    75  		})
    76  		require.NoError(t, err)
    77  
    78  		expectedResults := []any{
    79  			[]any{1, 2},
    80  			[]any{2, 4},
    81  			[]any{3, 6},
    82  		}
    83  		require.Equal(t, expectedResults, actualResults)
    84  		require.EqualValues(t, 3, ct.RowsAffected())
    85  	})
    86  }
    87  
    88  func TestForEachRowScanError(t *testing.T) {
    89  	t.Parallel()
    90  
    91  	ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
    92  	defer cancel()
    93  
    94  	pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
    95  		var actualResults []any
    96  
    97  		rows, _ := conn.Query(
    98  			context.Background(),
    99  			"select 'foo', 'bar' from generate_series(1, $1) n",
   100  			3,
   101  		)
   102  		var a, b int
   103  		ct, err := pgx.ForEachRow(rows, []any{&a, &b}, func() error {
   104  			actualResults = append(actualResults, []any{a, b})
   105  			return nil
   106  		})
   107  		require.EqualError(t, err, "can't scan into dest[0]: cannot scan text (OID 25) in text format into *int")
   108  		require.Equal(t, pgconn.CommandTag{}, ct)
   109  	})
   110  }
   111  
   112  func TestForEachRowAbort(t *testing.T) {
   113  	t.Parallel()
   114  
   115  	ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
   116  	defer cancel()
   117  
   118  	pgxtest.RunWithQueryExecModes(ctx, t, defaultConnTestRunner, nil, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   119  		rows, _ := conn.Query(
   120  			context.Background(),
   121  			"select n, n * 2 from generate_series(1, $1) n",
   122  			3,
   123  		)
   124  		var a, b int
   125  		ct, err := pgx.ForEachRow(rows, []any{&a, &b}, func() error {
   126  			return errors.New("abort")
   127  		})
   128  		require.EqualError(t, err, "abort")
   129  		require.Equal(t, pgconn.CommandTag{}, ct)
   130  	})
   131  }
   132  
   133  func ExampleForEachRow() {
   134  	conn, err := pgx.Connect(context.Background(), os.Getenv("PGX_TEST_DATABASE"))
   135  	if err != nil {
   136  		fmt.Printf("Unable to establish connection: %v", err)
   137  		return
   138  	}
   139  
   140  	rows, _ := conn.Query(
   141  		context.Background(),
   142  		"select n, n * 2 from generate_series(1, $1) n",
   143  		3,
   144  	)
   145  	var a, b int
   146  	_, err = pgx.ForEachRow(rows, []any{&a, &b}, func() error {
   147  		fmt.Printf("%v, %v\n", a, b)
   148  		return nil
   149  	})
   150  	if err != nil {
   151  		fmt.Printf("ForEachRow error: %v", err)
   152  		return
   153  	}
   154  
   155  	// Output:
   156  	// 1, 2
   157  	// 2, 4
   158  	// 3, 6
   159  }
   160  
   161  func TestCollectRows(t *testing.T) {
   162  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   163  		rows, _ := conn.Query(ctx, `select n from generate_series(0, 99) n`)
   164  		numbers, err := pgx.CollectRows(rows, func(row pgx.CollectableRow) (int32, error) {
   165  			var n int32
   166  			err := row.Scan(&n)
   167  			return n, err
   168  		})
   169  		require.NoError(t, err)
   170  
   171  		assert.Len(t, numbers, 100)
   172  		for i := range numbers {
   173  			assert.Equal(t, int32(i), numbers[i])
   174  		}
   175  	})
   176  }
   177  
   178  func TestCollectRowsEmpty(t *testing.T) {
   179  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   180  		rows, _ := conn.Query(ctx, `select n from generate_series(1, 0) n`)
   181  		numbers, err := pgx.CollectRows(rows, func(row pgx.CollectableRow) (int32, error) {
   182  			var n int32
   183  			err := row.Scan(&n)
   184  			return n, err
   185  		})
   186  		require.NoError(t, err)
   187  		require.NotNil(t, numbers)
   188  
   189  		assert.Empty(t, numbers)
   190  	})
   191  }
   192  
   193  // This example uses CollectRows with a manually written collector function. In most cases RowTo, RowToAddrOf,
   194  // RowToStructByPos, RowToAddrOfStructByPos, or another generic function would be used.
   195  func ExampleCollectRows() {
   196  	ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
   197  	defer cancel()
   198  
   199  	conn, err := pgx.Connect(ctx, os.Getenv("PGX_TEST_DATABASE"))
   200  	if err != nil {
   201  		fmt.Printf("Unable to establish connection: %v", err)
   202  		return
   203  	}
   204  
   205  	rows, _ := conn.Query(ctx, `select n from generate_series(1, 5) n`)
   206  	numbers, err := pgx.CollectRows(rows, func(row pgx.CollectableRow) (int32, error) {
   207  		var n int32
   208  		err := row.Scan(&n)
   209  		return n, err
   210  	})
   211  	if err != nil {
   212  		fmt.Printf("CollectRows error: %v", err)
   213  		return
   214  	}
   215  
   216  	fmt.Println(numbers)
   217  
   218  	// Output:
   219  	// [1 2 3 4 5]
   220  }
   221  
   222  func TestCollectOneRow(t *testing.T) {
   223  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   224  		rows, _ := conn.Query(ctx, `select 42`)
   225  		n, err := pgx.CollectOneRow(rows, func(row pgx.CollectableRow) (int32, error) {
   226  			var n int32
   227  			err := row.Scan(&n)
   228  			return n, err
   229  		})
   230  		assert.NoError(t, err)
   231  		assert.Equal(t, int32(42), n)
   232  	})
   233  }
   234  
   235  func TestCollectOneRowNotFound(t *testing.T) {
   236  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   237  		rows, _ := conn.Query(ctx, `select 42 where false`)
   238  		n, err := pgx.CollectOneRow(rows, func(row pgx.CollectableRow) (int32, error) {
   239  			var n int32
   240  			err := row.Scan(&n)
   241  			return n, err
   242  		})
   243  		assert.ErrorIs(t, err, pgx.ErrNoRows)
   244  		assert.Equal(t, int32(0), n)
   245  	})
   246  }
   247  
   248  func TestCollectOneRowIgnoresExtraRows(t *testing.T) {
   249  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   250  		rows, _ := conn.Query(ctx, `select n from generate_series(42, 99) n`)
   251  		n, err := pgx.CollectOneRow(rows, func(row pgx.CollectableRow) (int32, error) {
   252  			var n int32
   253  			err := row.Scan(&n)
   254  			return n, err
   255  		})
   256  		require.NoError(t, err)
   257  
   258  		assert.NoError(t, err)
   259  		assert.Equal(t, int32(42), n)
   260  	})
   261  }
   262  
   263  // https://github.com/jackc/pgx/issues/1334
   264  func TestCollectOneRowPrefersPostgreSQLErrorOverErrNoRows(t *testing.T) {
   265  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   266  		_, err := conn.Exec(ctx, `create temporary table t (name text not null unique)`)
   267  		require.NoError(t, err)
   268  
   269  		var name string
   270  		rows, _ := conn.Query(ctx, `insert into t (name) values ('foo') returning name`)
   271  		name, err = pgx.CollectOneRow(rows, func(row pgx.CollectableRow) (string, error) {
   272  			var n string
   273  			err := row.Scan(&n)
   274  			return n, err
   275  		})
   276  		require.NoError(t, err)
   277  		require.Equal(t, "foo", name)
   278  
   279  		rows, _ = conn.Query(ctx, `insert into t (name) values ('foo') returning name`)
   280  		name, err = pgx.CollectOneRow(rows, func(row pgx.CollectableRow) (string, error) {
   281  			var n string
   282  			err := row.Scan(&n)
   283  			return n, err
   284  		})
   285  		require.Error(t, err)
   286  		var pgErr *pgconn.PgError
   287  		require.ErrorAs(t, err, &pgErr)
   288  		require.Equal(t, "23505", pgErr.Code)
   289  		require.Equal(t, "", name)
   290  	})
   291  }
   292  
   293  func TestCollectExactlyOneRow(t *testing.T) {
   294  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   295  		rows, _ := conn.Query(ctx, `select 42`)
   296  		n, err := pgx.CollectExactlyOneRow(rows, func(row pgx.CollectableRow) (int32, error) {
   297  			var n int32
   298  			err := row.Scan(&n)
   299  			return n, err
   300  		})
   301  		assert.NoError(t, err)
   302  		assert.Equal(t, int32(42), n)
   303  	})
   304  }
   305  
   306  func TestCollectExactlyOneRowNotFound(t *testing.T) {
   307  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   308  		rows, _ := conn.Query(ctx, `select 42 where false`)
   309  		n, err := pgx.CollectExactlyOneRow(rows, func(row pgx.CollectableRow) (int32, error) {
   310  			var n int32
   311  			err := row.Scan(&n)
   312  			return n, err
   313  		})
   314  		assert.ErrorIs(t, err, pgx.ErrNoRows)
   315  		assert.Equal(t, int32(0), n)
   316  	})
   317  }
   318  
   319  func TestCollectExactlyOneRowExtraRows(t *testing.T) {
   320  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   321  		rows, _ := conn.Query(ctx, `select n from generate_series(42, 99) n`)
   322  		n, err := pgx.CollectExactlyOneRow(rows, func(row pgx.CollectableRow) (int32, error) {
   323  			var n int32
   324  			err := row.Scan(&n)
   325  			return n, err
   326  		})
   327  		assert.ErrorIs(t, err, pgx.ErrTooManyRows)
   328  		assert.Equal(t, int32(0), n)
   329  	})
   330  }
   331  
   332  func TestRowTo(t *testing.T) {
   333  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   334  		rows, _ := conn.Query(ctx, `select n from generate_series(0, 99) n`)
   335  		numbers, err := pgx.CollectRows(rows, pgx.RowTo[int32])
   336  		require.NoError(t, err)
   337  
   338  		assert.Len(t, numbers, 100)
   339  		for i := range numbers {
   340  			assert.Equal(t, int32(i), numbers[i])
   341  		}
   342  	})
   343  }
   344  
   345  func ExampleRowTo() {
   346  	ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
   347  	defer cancel()
   348  
   349  	conn, err := pgx.Connect(ctx, os.Getenv("PGX_TEST_DATABASE"))
   350  	if err != nil {
   351  		fmt.Printf("Unable to establish connection: %v", err)
   352  		return
   353  	}
   354  
   355  	rows, _ := conn.Query(ctx, `select n from generate_series(1, 5) n`)
   356  	numbers, err := pgx.CollectRows(rows, pgx.RowTo[int32])
   357  	if err != nil {
   358  		fmt.Printf("CollectRows error: %v", err)
   359  		return
   360  	}
   361  
   362  	fmt.Println(numbers)
   363  
   364  	// Output:
   365  	// [1 2 3 4 5]
   366  }
   367  
   368  func TestRowToAddrOf(t *testing.T) {
   369  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   370  		rows, _ := conn.Query(ctx, `select n from generate_series(0, 99) n`)
   371  		numbers, err := pgx.CollectRows(rows, pgx.RowToAddrOf[int32])
   372  		require.NoError(t, err)
   373  
   374  		assert.Len(t, numbers, 100)
   375  		for i := range numbers {
   376  			assert.Equal(t, int32(i), *numbers[i])
   377  		}
   378  	})
   379  }
   380  
   381  func ExampleRowToAddrOf() {
   382  	ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
   383  	defer cancel()
   384  
   385  	conn, err := pgx.Connect(ctx, os.Getenv("PGX_TEST_DATABASE"))
   386  	if err != nil {
   387  		fmt.Printf("Unable to establish connection: %v", err)
   388  		return
   389  	}
   390  
   391  	rows, _ := conn.Query(ctx, `select n from generate_series(1, 5) n`)
   392  	pNumbers, err := pgx.CollectRows(rows, pgx.RowToAddrOf[int32])
   393  	if err != nil {
   394  		fmt.Printf("CollectRows error: %v", err)
   395  		return
   396  	}
   397  
   398  	for _, p := range pNumbers {
   399  		fmt.Println(*p)
   400  	}
   401  
   402  	// Output:
   403  	// 1
   404  	// 2
   405  	// 3
   406  	// 4
   407  	// 5
   408  }
   409  
   410  func TestRowToMap(t *testing.T) {
   411  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   412  		rows, _ := conn.Query(ctx, `select 'Joe' as name, n as age from generate_series(0, 9) n`)
   413  		slice, err := pgx.CollectRows(rows, pgx.RowToMap)
   414  		require.NoError(t, err)
   415  
   416  		assert.Len(t, slice, 10)
   417  		for i := range slice {
   418  			assert.Equal(t, "Joe", slice[i]["name"])
   419  			assert.EqualValues(t, i, slice[i]["age"])
   420  		}
   421  	})
   422  }
   423  
   424  func TestRowToStructByPos(t *testing.T) {
   425  	type person struct {
   426  		Name string
   427  		Age  int32
   428  	}
   429  
   430  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   431  		rows, _ := conn.Query(ctx, `select 'Joe' as name, n as age from generate_series(0, 9) n`)
   432  		slice, err := pgx.CollectRows(rows, pgx.RowToStructByPos[person])
   433  		require.NoError(t, err)
   434  
   435  		assert.Len(t, slice, 10)
   436  		for i := range slice {
   437  			assert.Equal(t, "Joe", slice[i].Name)
   438  			assert.EqualValues(t, i, slice[i].Age)
   439  		}
   440  	})
   441  }
   442  
   443  func TestRowToStructByPosIgnoredField(t *testing.T) {
   444  	type person struct {
   445  		Name string
   446  		Age  int32 `db:"-"`
   447  	}
   448  
   449  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   450  		rows, _ := conn.Query(ctx, `select 'Joe' as name from generate_series(0, 9) n`)
   451  		slice, err := pgx.CollectRows(rows, pgx.RowToStructByPos[person])
   452  		require.NoError(t, err)
   453  
   454  		assert.Len(t, slice, 10)
   455  		for i := range slice {
   456  			assert.Equal(t, "Joe", slice[i].Name)
   457  		}
   458  	})
   459  }
   460  
   461  func TestRowToStructByPosEmbeddedStruct(t *testing.T) {
   462  	type Name struct {
   463  		First string
   464  		Last  string
   465  	}
   466  
   467  	type person struct {
   468  		Name
   469  		Age int32
   470  	}
   471  
   472  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   473  		rows, _ := conn.Query(ctx, `select 'John' as first_name, 'Smith' as last_name, n as age from generate_series(0, 9) n`)
   474  		slice, err := pgx.CollectRows(rows, pgx.RowToStructByPos[person])
   475  		require.NoError(t, err)
   476  
   477  		assert.Len(t, slice, 10)
   478  		for i := range slice {
   479  			assert.Equal(t, "John", slice[i].Name.First)
   480  			assert.Equal(t, "Smith", slice[i].Name.Last)
   481  			assert.EqualValues(t, i, slice[i].Age)
   482  		}
   483  	})
   484  }
   485  
   486  func TestRowToStructByPosMultipleEmbeddedStruct(t *testing.T) {
   487  	type Sandwich struct {
   488  		Bread string
   489  		Salad string
   490  	}
   491  	type Drink struct {
   492  		Ml int
   493  	}
   494  
   495  	type meal struct {
   496  		Sandwich
   497  		Drink
   498  	}
   499  
   500  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   501  		rows, _ := conn.Query(ctx, `select 'Baguette' as bread, 'Lettuce' as salad, drink_ml from generate_series(0, 9) drink_ml`)
   502  		slice, err := pgx.CollectRows(rows, pgx.RowToStructByPos[meal])
   503  		require.NoError(t, err)
   504  
   505  		assert.Len(t, slice, 10)
   506  		for i := range slice {
   507  			assert.Equal(t, "Baguette", slice[i].Sandwich.Bread)
   508  			assert.Equal(t, "Lettuce", slice[i].Sandwich.Salad)
   509  			assert.EqualValues(t, i, slice[i].Drink.Ml)
   510  		}
   511  	})
   512  }
   513  
   514  func TestRowToStructByPosEmbeddedUnexportedStruct(t *testing.T) {
   515  	type name struct {
   516  		First string
   517  		Last  string
   518  	}
   519  
   520  	type person struct {
   521  		name
   522  		Age int32
   523  	}
   524  
   525  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   526  		rows, _ := conn.Query(ctx, `select 'John' as first_name, 'Smith' as last_name, n as age from generate_series(0, 9) n`)
   527  		slice, err := pgx.CollectRows(rows, pgx.RowToStructByPos[person])
   528  		require.NoError(t, err)
   529  
   530  		assert.Len(t, slice, 10)
   531  		for i := range slice {
   532  			assert.Equal(t, "John", slice[i].name.First)
   533  			assert.Equal(t, "Smith", slice[i].name.Last)
   534  			assert.EqualValues(t, i, slice[i].Age)
   535  		}
   536  	})
   537  }
   538  
   539  // Pointer to struct is not supported. But check that we don't panic.
   540  func TestRowToStructByPosEmbeddedPointerToStruct(t *testing.T) {
   541  	type Name struct {
   542  		First string
   543  		Last  string
   544  	}
   545  
   546  	type person struct {
   547  		*Name
   548  		Age int32
   549  	}
   550  
   551  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   552  		rows, _ := conn.Query(ctx, `select 'John' as first_name, 'Smith' as last_name, n as age from generate_series(0, 9) n`)
   553  		_, err := pgx.CollectRows(rows, pgx.RowToStructByPos[person])
   554  		require.EqualError(t, err, "got 3 values, but dst struct has only 2 fields")
   555  	})
   556  }
   557  
   558  func ExampleRowToStructByPos() {
   559  	ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
   560  	defer cancel()
   561  
   562  	conn, err := pgx.Connect(ctx, os.Getenv("PGX_TEST_DATABASE"))
   563  	if err != nil {
   564  		fmt.Printf("Unable to establish connection: %v", err)
   565  		return
   566  	}
   567  
   568  	if conn.PgConn().ParameterStatus("crdb_version") != "" {
   569  		// Skip test / example when running on CockroachDB. Since an example can't be skipped fake success instead.
   570  		fmt.Println(`Cheeseburger: $10
   571  Fries: $5
   572  Soft Drink: $3`)
   573  		return
   574  	}
   575  
   576  	// Setup example schema and data.
   577  	_, err = conn.Exec(ctx, `
   578  create temporary table products (
   579  	id int primary key generated by default as identity,
   580  	name varchar(100) not null,
   581  	price int not null
   582  );
   583  
   584  insert into products (name, price) values
   585  	('Cheeseburger', 10),
   586  	('Double Cheeseburger', 14),
   587  	('Fries', 5),
   588  	('Soft Drink', 3);
   589  `)
   590  	if err != nil {
   591  		fmt.Printf("Unable to setup example schema and data: %v", err)
   592  		return
   593  	}
   594  
   595  	type product struct {
   596  		ID    int32
   597  		Name  string
   598  		Price int32
   599  	}
   600  
   601  	rows, _ := conn.Query(ctx, "select * from products where price < $1 order by price desc", 12)
   602  	products, err := pgx.CollectRows(rows, pgx.RowToStructByPos[product])
   603  	if err != nil {
   604  		fmt.Printf("CollectRows error: %v", err)
   605  		return
   606  	}
   607  
   608  	for _, p := range products {
   609  		fmt.Printf("%s: $%d\n", p.Name, p.Price)
   610  	}
   611  
   612  	// Output:
   613  	// Cheeseburger: $10
   614  	// Fries: $5
   615  	// Soft Drink: $3
   616  }
   617  
   618  func TestRowToAddrOfStructPos(t *testing.T) {
   619  	type person struct {
   620  		Name string
   621  		Age  int32
   622  	}
   623  
   624  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   625  		rows, _ := conn.Query(ctx, `select 'Joe' as name, n as age from generate_series(0, 9) n`)
   626  		slice, err := pgx.CollectRows(rows, pgx.RowToAddrOfStructByPos[person])
   627  		require.NoError(t, err)
   628  
   629  		assert.Len(t, slice, 10)
   630  		for i := range slice {
   631  			assert.Equal(t, "Joe", slice[i].Name)
   632  			assert.EqualValues(t, i, slice[i].Age)
   633  		}
   634  	})
   635  }
   636  
   637  func TestRowToStructByName(t *testing.T) {
   638  	type person struct {
   639  		Last      string
   640  		First     string
   641  		Age       int32
   642  		AccountID string
   643  	}
   644  
   645  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   646  		rows, _ := conn.Query(ctx, `select 'John' as first, 'Smith' as last, n as age, 'd5e49d3f' as account_id from generate_series(0, 9) n`)
   647  		slice, err := pgx.CollectRows(rows, pgx.RowToStructByName[person])
   648  		assert.NoError(t, err)
   649  
   650  		assert.Len(t, slice, 10)
   651  		for i := range slice {
   652  			assert.Equal(t, "Smith", slice[i].Last)
   653  			assert.Equal(t, "John", slice[i].First)
   654  			assert.EqualValues(t, i, slice[i].Age)
   655  			assert.Equal(t, "d5e49d3f", slice[i].AccountID)
   656  		}
   657  
   658  		// check missing fields in a returned row
   659  		rows, _ = conn.Query(ctx, `select 'Smith' as last, n as age from generate_series(0, 9) n`)
   660  		_, err = pgx.CollectRows(rows, pgx.RowToStructByName[person])
   661  		assert.ErrorContains(t, err, "cannot find field First in returned row")
   662  
   663  		// check missing field in a destination struct
   664  		rows, _ = conn.Query(ctx, `select 'John' as first, 'Smith' as last, n as age, 'd5e49d3f' as account_id, null as ignore from generate_series(0, 9) n`)
   665  		_, err = pgx.CollectRows(rows, pgx.RowToAddrOfStructByName[person])
   666  		assert.ErrorContains(t, err, "struct doesn't have corresponding row field ignore")
   667  	})
   668  }
   669  
   670  func TestRowToStructByNameEmbeddedStruct(t *testing.T) {
   671  	type Name struct {
   672  		Last  string `db:"last_name"`
   673  		First string `db:"first_name"`
   674  	}
   675  
   676  	type person struct {
   677  		Ignore bool `db:"-"`
   678  		Name
   679  		Age int32
   680  	}
   681  
   682  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   683  		rows, _ := conn.Query(ctx, `select 'John' as first_name, 'Smith' as last_name, n as age from generate_series(0, 9) n`)
   684  		slice, err := pgx.CollectRows(rows, pgx.RowToStructByName[person])
   685  		assert.NoError(t, err)
   686  
   687  		assert.Len(t, slice, 10)
   688  		for i := range slice {
   689  			assert.Equal(t, "Smith", slice[i].Name.Last)
   690  			assert.Equal(t, "John", slice[i].Name.First)
   691  			assert.EqualValues(t, i, slice[i].Age)
   692  		}
   693  
   694  		// check missing fields in a returned row
   695  		rows, _ = conn.Query(ctx, `select 'Smith' as last_name, n as age from generate_series(0, 9) n`)
   696  		_, err = pgx.CollectRows(rows, pgx.RowToStructByName[person])
   697  		assert.ErrorContains(t, err, "cannot find field first_name in returned row")
   698  
   699  		// check missing field in a destination struct
   700  		rows, _ = conn.Query(ctx, `select 'John' as first_name, 'Smith' as last_name, n as age, null as ignore from generate_series(0, 9) n`)
   701  		_, err = pgx.CollectRows(rows, pgx.RowToAddrOfStructByName[person])
   702  		assert.ErrorContains(t, err, "struct doesn't have corresponding row field ignore")
   703  	})
   704  }
   705  
   706  func ExampleRowToStructByName() {
   707  	ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
   708  	defer cancel()
   709  
   710  	conn, err := pgx.Connect(ctx, os.Getenv("PGX_TEST_DATABASE"))
   711  	if err != nil {
   712  		fmt.Printf("Unable to establish connection: %v", err)
   713  		return
   714  	}
   715  
   716  	if conn.PgConn().ParameterStatus("crdb_version") != "" {
   717  		// Skip test / example when running on CockroachDB. Since an example can't be skipped fake success instead.
   718  		fmt.Println(`Cheeseburger: $10
   719  Fries: $5
   720  Soft Drink: $3`)
   721  		return
   722  	}
   723  
   724  	// Setup example schema and data.
   725  	_, err = conn.Exec(ctx, `
   726  create temporary table products (
   727  	id int primary key generated by default as identity,
   728  	name varchar(100) not null,
   729  	price int not null
   730  );
   731  
   732  insert into products (name, price) values
   733  	('Cheeseburger', 10),
   734  	('Double Cheeseburger', 14),
   735  	('Fries', 5),
   736  	('Soft Drink', 3);
   737  `)
   738  	if err != nil {
   739  		fmt.Printf("Unable to setup example schema and data: %v", err)
   740  		return
   741  	}
   742  
   743  	type product struct {
   744  		ID    int32
   745  		Name  string
   746  		Price int32
   747  	}
   748  
   749  	rows, _ := conn.Query(ctx, "select * from products where price < $1 order by price desc", 12)
   750  	products, err := pgx.CollectRows(rows, pgx.RowToStructByName[product])
   751  	if err != nil {
   752  		fmt.Printf("CollectRows error: %v", err)
   753  		return
   754  	}
   755  
   756  	for _, p := range products {
   757  		fmt.Printf("%s: $%d\n", p.Name, p.Price)
   758  	}
   759  
   760  	// Output:
   761  	// Cheeseburger: $10
   762  	// Fries: $5
   763  	// Soft Drink: $3
   764  }
   765  
   766  func TestRowToStructByNameLax(t *testing.T) {
   767  	type person struct {
   768  		Last   string
   769  		First  string
   770  		Age    int32
   771  		Ignore bool `db:"-"`
   772  	}
   773  
   774  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   775  		rows, _ := conn.Query(ctx, `select 'John' as first, 'Smith' as last, n as age from generate_series(0, 9) n`)
   776  		slice, err := pgx.CollectRows(rows, pgx.RowToStructByNameLax[person])
   777  		assert.NoError(t, err)
   778  
   779  		assert.Len(t, slice, 10)
   780  		for i := range slice {
   781  			assert.Equal(t, "Smith", slice[i].Last)
   782  			assert.Equal(t, "John", slice[i].First)
   783  			assert.EqualValues(t, i, slice[i].Age)
   784  		}
   785  
   786  		// check missing fields in a returned row
   787  		rows, _ = conn.Query(ctx, `select 'John' as first, n as age from generate_series(0, 9) n`)
   788  		slice, err = pgx.CollectRows(rows, pgx.RowToStructByNameLax[person])
   789  		assert.NoError(t, err)
   790  
   791  		assert.Len(t, slice, 10)
   792  		for i := range slice {
   793  			assert.Equal(t, "John", slice[i].First)
   794  			assert.EqualValues(t, i, slice[i].Age)
   795  		}
   796  
   797  		// check extra fields in a returned row
   798  		rows, _ = conn.Query(ctx, `select 'John' as first, 'Smith' as last, n as age, null as ignore from generate_series(0, 9) n`)
   799  		_, err = pgx.CollectRows(rows, pgx.RowToAddrOfStructByNameLax[person])
   800  		assert.ErrorContains(t, err, "struct doesn't have corresponding row field ignore")
   801  
   802  		// check missing fields in a destination struct
   803  		rows, _ = conn.Query(ctx, `select 'Smith' as last, 'D.' as middle, n as age from generate_series(0, 9) n`)
   804  		_, err = pgx.CollectRows(rows, pgx.RowToAddrOfStructByNameLax[person])
   805  		assert.ErrorContains(t, err, "struct doesn't have corresponding row field middle")
   806  
   807  		// check ignored fields in a destination struct
   808  		rows, _ = conn.Query(ctx, `select 'Smith' as last, n as age, null as ignore from generate_series(0, 9) n`)
   809  		_, err = pgx.CollectRows(rows, pgx.RowToAddrOfStructByNameLax[person])
   810  		assert.ErrorContains(t, err, "struct doesn't have corresponding row field ignore")
   811  	})
   812  }
   813  
   814  func TestRowToStructByNameLaxEmbeddedStruct(t *testing.T) {
   815  	type Name struct {
   816  		Last  string `db:"last_name"`
   817  		First string `db:"first_name"`
   818  	}
   819  
   820  	type person struct {
   821  		Ignore bool `db:"-"`
   822  		Name
   823  		Age int32
   824  	}
   825  
   826  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   827  		rows, _ := conn.Query(ctx, `select 'John' as first_name, 'Smith' as last_name, n as age from generate_series(0, 9) n`)
   828  		slice, err := pgx.CollectRows(rows, pgx.RowToStructByNameLax[person])
   829  		assert.NoError(t, err)
   830  
   831  		assert.Len(t, slice, 10)
   832  		for i := range slice {
   833  			assert.Equal(t, "Smith", slice[i].Name.Last)
   834  			assert.Equal(t, "John", slice[i].Name.First)
   835  			assert.EqualValues(t, i, slice[i].Age)
   836  		}
   837  
   838  		// check missing fields in a returned row
   839  		rows, _ = conn.Query(ctx, `select 'John' as first_name, n as age from generate_series(0, 9) n`)
   840  		slice, err = pgx.CollectRows(rows, pgx.RowToStructByNameLax[person])
   841  		assert.NoError(t, err)
   842  
   843  		assert.Len(t, slice, 10)
   844  		for i := range slice {
   845  			assert.Equal(t, "John", slice[i].Name.First)
   846  			assert.EqualValues(t, i, slice[i].Age)
   847  		}
   848  
   849  		// check extra fields in a returned row
   850  		rows, _ = conn.Query(ctx, `select 'John' as first_name, 'Smith' as last_name, n as age, null as ignore from generate_series(0, 9) n`)
   851  		_, err = pgx.CollectRows(rows, pgx.RowToAddrOfStructByNameLax[person])
   852  		assert.ErrorContains(t, err, "struct doesn't have corresponding row field ignore")
   853  
   854  		// check missing fields in a destination struct
   855  		rows, _ = conn.Query(ctx, `select 'Smith' as last_name, 'D.' as middle_name, n as age from generate_series(0, 9) n`)
   856  		_, err = pgx.CollectRows(rows, pgx.RowToAddrOfStructByNameLax[person])
   857  		assert.ErrorContains(t, err, "struct doesn't have corresponding row field middle_name")
   858  
   859  		// check ignored fields in a destination struct
   860  		rows, _ = conn.Query(ctx, `select 'Smith' as last_name, n as age, null as ignore from generate_series(0, 9) n`)
   861  		_, err = pgx.CollectRows(rows, pgx.RowToAddrOfStructByNameLax[person])
   862  		assert.ErrorContains(t, err, "struct doesn't have corresponding row field ignore")
   863  	})
   864  }
   865  
   866  func TestRowToStructByNameLaxRowValue(t *testing.T) {
   867  	type AnotherTable struct{}
   868  	type User struct {
   869  		UserID int    `json:"userId" db:"user_id"`
   870  		Name   string `json:"name" db:"name"`
   871  	}
   872  	type UserAPIKey struct {
   873  		UserAPIKeyID int `json:"userApiKeyId" db:"user_api_key_id"`
   874  		UserID       int `json:"userId" db:"user_id"`
   875  
   876  		User         *User         `json:"user" db:"user"`
   877  		AnotherTable *AnotherTable `json:"anotherTable" db:"another_table"`
   878  	}
   879  
   880  	defaultConnTestRunner.RunTest(context.Background(), t, func(ctx context.Context, t testing.TB, conn *pgx.Conn) {
   881  		pgxtest.SkipCockroachDB(t, conn, "")
   882  
   883  		rows, _ := conn.Query(ctx, `
   884  		WITH user_api_keys AS (
   885  			SELECT 1 AS user_id, 101 AS user_api_key_id, 'abc123' AS api_key
   886  		), users AS (
   887  			SELECT 1 AS user_id, 'John Doe' AS name
   888  		)
   889  		SELECT user_api_keys.user_api_key_id, user_api_keys.user_id, row(users.*) AS user
   890  		FROM user_api_keys
   891  		LEFT JOIN users ON users.user_id = user_api_keys.user_id
   892  		WHERE user_api_keys.api_key = 'abc123';
   893  		`)
   894  		slice, err := pgx.CollectRows(rows, pgx.RowToStructByNameLax[UserAPIKey])
   895  
   896  		assert.NoError(t, err)
   897  		assert.ElementsMatch(t, slice, []UserAPIKey{{UserAPIKeyID: 101, UserID: 1, User: &User{UserID: 1, Name: "John Doe"}, AnotherTable: nil}})
   898  	})
   899  }
   900  
   901  func ExampleRowToStructByNameLax() {
   902  	ctx, cancel := context.WithTimeout(context.Background(), 120*time.Second)
   903  	defer cancel()
   904  
   905  	conn, err := pgx.Connect(ctx, os.Getenv("PGX_TEST_DATABASE"))
   906  	if err != nil {
   907  		fmt.Printf("Unable to establish connection: %v", err)
   908  		return
   909  	}
   910  
   911  	if conn.PgConn().ParameterStatus("crdb_version") != "" {
   912  		// Skip test / example when running on CockroachDB. Since an example can't be skipped fake success instead.
   913  		fmt.Println(`Cheeseburger: $10
   914  Fries: $5
   915  Soft Drink: $3`)
   916  		return
   917  	}
   918  
   919  	// Setup example schema and data.
   920  	_, err = conn.Exec(ctx, `
   921  create temporary table products (
   922  	id int primary key generated by default as identity,
   923  	name varchar(100) not null,
   924  	price int not null
   925  );
   926  
   927  insert into products (name, price) values
   928  	('Cheeseburger', 10),
   929  	('Double Cheeseburger', 14),
   930  	('Fries', 5),
   931  	('Soft Drink', 3);
   932  `)
   933  	if err != nil {
   934  		fmt.Printf("Unable to setup example schema and data: %v", err)
   935  		return
   936  	}
   937  
   938  	type product struct {
   939  		ID    int32
   940  		Name  string
   941  		Type  string
   942  		Price int32
   943  	}
   944  
   945  	rows, _ := conn.Query(ctx, "select * from products where price < $1 order by price desc", 12)
   946  	products, err := pgx.CollectRows(rows, pgx.RowToStructByNameLax[product])
   947  	if err != nil {
   948  		fmt.Printf("CollectRows error: %v", err)
   949  		return
   950  	}
   951  
   952  	for _, p := range products {
   953  		fmt.Printf("%s: $%d\n", p.Name, p.Price)
   954  	}
   955  
   956  	// Output:
   957  	// Cheeseburger: $10
   958  	// Fries: $5
   959  	// Soft Drink: $3
   960  }
   961  

View as plain text