使用GORM和Postgresql如何设置search_path？

How can I get the current schema from gorm? Normally it is "SHOW search_path" and is an exec. Same with setting the current search_path normally "set search_path to newschema". where new schema can be a single schema name of a series of names.

I attempted db.Exec("show search_path").Find(&result) where result is a []string and I get an empty array.

FOUND how to display current schema by experimenting and a hint about Raw and Scan in PluralSite's GORM class.

var currentSchema string
rows, err := db.Debug().Raw("show search_path").Rows()
rows.Next()  // to get the first and only result
rows.Scan(&currentSchema)
fmt.Printf("Search Path: %v
", currentSchema)

returns: Search Path: "$user", public

Taking @PavloStrokov point about transactions (which are basically threads and a transaction) into account, I was able to get the schema to stay during the current process. In the handler, I use the existing DB connection and create a transaction. It gets passed to every method that is called that touches the Database. I change the call to the gorm method to be the passed tx instead of the normal db variable that was stored and made available to any method that needed it.

The call I make to start it all of is:

tx := db.Begin()

tx.Exec("set search_path to schema_name")

The string is generated to use the proper Facility schema name.

The tx is the last param in the signature of all methods that touch the DB. For example func methiodUsingDBSchema(val1 string, val2 string, tx *gorm.DB){}

Then the last thing I do is tx.Commit() Even though these are all reads.

I have run all test and works well.

I use the Postgres schemas to separate Facilities in my application and need to change based upon the user logged in. All User and Facility information is in public.

Setting search_path by executing query like set search_path to newschema is incorrect way of setting, because this will be changed only for the connection that was used to execute the query.

To set it to full set of connection that your application will use you need to provide search_path as an argument to gorm.Open("postgres", "dbname=YourDB search_path=YourSearchPath") with all other connection arguments you gonna to use like dbname or port, user, etc.