Example of set custom search_path with using pgxpool

main.go

package main

import (
	"context"
	"fmt"
	"log"
	"os"

	"github.com/jackc/pgx/v5/pgxpool"
)

func main() {
	// Database URL can be from environment variable or hardcoded for example purposes
	dbUrl := os.Getenv("DATABASE_URL")
	if dbUrl == "" {
		dbUrl = "postgres://postgres:postgres@localhost:5432/postgres" // Replace with your actual connection string
	}

	dbPoolConfig, err := pgxpool.ParseConfig(dbUrl)
	if err != nil {
		log.Fatalf("Unable to parse connection string: %v\n", err)
	}

	// Set the schema in search path for all connections in the pool
	dbSearchPath := os.Getenv("DATABASE_SEARCH_PATH")
	if dbSearchPath != "" {
		dbPoolConfig.ConnConfig.RuntimeParams["search_path"] = dbSearchPath
	}

	dbPool, err := pgxpool.NewWithConfig(context.Background(), dbPoolConfig)
	if err != nil {
		log.Fatalf("Unable to create connection pool: %v\n", err)
	}
	defer dbPool.Close()

	var queryResult string
	err = dbPool.QueryRow(context.Background(), "SELECT version();").Scan(&queryResult)
	if err != nil {
		log.Fatalf("QueryRow failed: %v\n", err)
	}
	fmt.Printf("QueryResult: %s\n", queryResult)

	err = dbPool.QueryRow(context.Background(), "SHOW search_path;").Scan(&queryResult)
	if err != nil {
		log.Fatalf("QueryRow failed: %v\n", err)
	}
	fmt.Printf("QueryResult: %s\n", queryResult)

	err = dbPool.QueryRow(context.Background(), "SELECT count(*) FROM products;").Scan(&queryResult)
	if err != nil {
		log.Fatalf("QueryRow failed: %v\n", err)
	}
	fmt.Printf("QueryResult: %s\n", queryResult)
}