Gorm Trying to set pgsql current_app_user using parameterized statement but fails

Your Question

Im trying to set current_app_user using a parametrized statement, but apparently this is failing and I cant seems to set the current user. this way

    s.DB.Connection(func(tx *gorm.DB) error {
        meta := &[]model.Meta{}
        tx.Exec("SET app.current_app_user = ?", user)
        tx.Raw("SELECT * from meta").Scan(&meta)
        PrettyPrint(meta)
        return nil
    })

this is what im trying to do, just for testing I tried to format string and set the current user and it worked fine like shown below

tx.Exec(fmt.Sprintf("SET app.current_app_user = '%s'", user))

this worked so I assume this is something to do with the parameterized statements

The document you expected this should be explained

just for context what I'm trying to do is my pgsql instance has a row level policy setup and I'm trying to execute a query but I'm not getting anydata since the current_app_user is not set this works if i set the value without parametrized statement

I think it would be helpful to see this explained in the database sections on the gorm doc since its a unique thing for postgresql

Expected answer

would like to know if someone knows how to fix this issue

Comment From: ebadfd

2023/03/08 17:24:50 internal/service/user.go:34 ERROR: syntax error at or near "$1" (SQLSTATE 42601)
[3.171ms] [rows:0] SET app.current_app_user = '487008ae-cf39-49c1-9247-8553d5cb4a86'

2023/03/08 17:24:50 /internal/service/user.go:35 ERROR: syntax error at or near "$1" (SQLSTATE 42601)
[0.003ms] [rows:0] SELECT * from meta

this is what im seeing on the logs

Comment From: a631807682

pgx will enable prepare stmt by default, you need to disable it. https://github.com/go-gorm/postgres#configuration

        tx.Scopes(func(d *gorm.DB) *gorm.DB {
            // ignore prepare stmt
            d.Statement.Vars = append([]interface{}{pgx.QueryExecModeSimpleProtocol}, d.Statement.Vars...)
            return d
        }).Exec("SET app.current_app_user = ?", user)