英文:
Executing SQL query with variable number of named parameters in Golang
问题
我有一个 PostgreSQL 函数，它接受可变数量的命名参数，并返回相应项的列表：
CREATE OR REPLACE FUNCTION read_user(_id BIGINT DEFAULT NULL,_phone VARCHAR(30) DEFAULT NULL,_type VARCHAR(15) DEFAULT NULL,_last VARCHAR(50) DEFAULT NULL,_first VARCHAR(50) DEFAULT NULL)RETURNS setof T_USERSAS $$BEGINRETURN QUERYSELECT * FROM T_USERSWHERE ( id = _id OR _id IS NULL )AND ( phone = _phone OR _phone IS NULL )AND ( type = _type OR _type IS NULL )AND ( last = _last OR _last IS NULL )AND ( first = _first OR _first IS NULL );EXCEPTION WHEN others THENRAISE WARNING 'Transaction failed and was rolled back';RAISE NOTICE '% %', SQLERRM, SQLSTATE;END$$ LANGUAGE plpgsql;
我可以运行类似这样的多态查询：
SELECT read_user(_id := 2);SELECT read_user(_first := 'John', _last := 'Doe');
在 Golang 中，我可以这样做：
stmt, err := db.Prepare("SELECT read_user(_id = ?)")
但是，如何使用可变数量的 read_user 参数做同样的事情呢？我正在使用 pq 驱动程序 https://github.com/lib/pq。
英文:
So I have this PostgreSQL function, which takes variable number of named arguments and returns list of corresponding items:
CREATE OR REPLACE FUNCTION read_user(_id BIGINT DEFAULT NULL,_phone VARCHAR(30) DEFAULT NULL,_type VARCHAR(15) DEFAULT NULL,_last VARCHAR(50) DEFAULT NULL,_first VARCHAR(50) DEFAULT NULL)RETURNS setof T_USERSAS $$BEGINRETURN QUERYSELECT * FROM T_USERSWHERE ( id = _id OR _id IS NULL )AND ( phone = _phone OR _phone IS NULL )AND ( type = _type OR _type IS NULL )AND ( last = _last OR _last IS NULL )AND ( first = _first OR _first IS NULL );EXCEPTION WHEN others THENRAISE WARNING 'Transaction failed and was rolled back';RAISE NOTICE '% %', SQLERRM, SQLSTATE;END$$ LANGUAGE plpgsql;
So I can run polymorphic queries like these:
SELECT read_user(_id := 2);SELECT read_user(_first := 'John', _last := 'Doe');
In Golang I can make something like:
stmt, err := db.Prepare("SELECT read_user(_id = ?)")
But how can I do the same, but with variable amount of read_user arguments? I'm using pq driver https://github.com/lib/pq.
答案1
得分: 4
你可以通过列举所有参数及其占位符来构建自己的语句，然后在没有参数值的地方显式地传递nil。
stmt, err := db.Prepare("SELECT read_user(_id := $1, _phone := $2, _type := $3, _last := $4, _first := $5)")if err != nil {// ...}stmt.Query(2, nil, nil, nil, nil) // 结果应等同于 `SELECT read_user(_id := 2)`stmt.Query(nil, nil, nil, "Doe", "John") // 结果应等同于 `SELECT read_user(_first := 'John', _last := 'Doe')`
如果你也想在Go中使用命名参数，你可以创建一个结构体类型来表示参数，并创建一个包装函数，将该参数类型的字段映射到查询中：
type readUserParams struct {Id interface{}Phone interface{}Type interface{}Last interface{}First interface{}}func readUser(p *readUserParams) {stmt.Query(p.Id, p.Phone, p.Type, p.Last, p.First)// ...}readUser(&readUserParams{Id: 2})readUser(&readUserParams{First: "John", Last: "Doe"})
英文:
You can construct your one statement by enumerating all the parameters with their placeholders and then you could pass nil explicitly where you don't have the parameter value.
stmt, err := db.Prepare("SELECT read_user(_id := $1, _phone := $2, _type := $3, _last := $4, _first := $5)")if err != nil {// ...}stmt.Query(2, nil, nil, nil, nil) // result should be equivalent to `SELECT read_user(_id := 2)`stmt.Query(nil, nil, nil, "Doe", "John") // result should be equivalent to `SELECT read_user(_first := 'John', _last := 'Doe')`
And if you want to have named parameters in Go as well, you can create a struct type to represent the parameters and a wrapper func that'll map that parameter type's fields into the query:
type readUserParams struct {Id interface{}Phone interface{}Type interface{}Last interface{}First interface{}}func readUser(p *readUserParams) {stmt.Query(p.Id, p.Phone, p.Type, p.Last, p.First)// ...}readUser(&readUserParams{Id: 2})readUser(&readUserParams{First: "John", Last:"Doe"})
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论