在Golang中使用可变数量的命名参数执行SQL查询

问题描述：

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_USERS
AS $$ 
BEGIN
  RETURN QUERY
  SELECT * FROM T_USERS
  WHERE ( 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 THEN
      RAISE 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.

所以我有这个PostgreSQL函数，该函数采用可变数量的命名参数并返回相应项的列表： p >

 创建或替换功能read_user（
 _id BIGINT默认为空，
 _phone VARCHAR（30）默认为空，
 _type VARCHAR（15）默认为空，
 _last VARCHAR（50  ）DEFAULT NULL，
 _first VARCHAR（50）DEFAULT NULL 
）
RETURNS set of T_USERS 
AS $$ 
BEGIN 
 RETURN QUERY 
 SELECT * FROM T_USERS 
 WHERE（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）; 
例外 当其他人
发出警告时'交易失败并被回滚'; 
引起注意'％％'，SQLERRM，SQLSTATE; 
END 
 $$语言plpgsql; 
  code>  pre> 
  
 所以我可以运行像这样的多态查询： p> 
 
 
  SELECT read_user（_id：= 2）; 
SELECT read_user（_fir  st：='John'，_last：='Doe'）; 
  code>  pre> 
 
 在Golang中，我可以做类似的事情： p> 
 
 
  stmt，err：= db.Prepare（“ SELECT read_user（_id =？）”“ 
  code>  pre> 
 
 但是我该怎么做，但是  read_user  code>参数的数量可变？ 我正在使用 pq  code>驱动程序 https://github.com/lib/pq  a>。 p> 
  div>

答

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"})

在Golang中使用可变数量的命名参数执行SQL查询

相关推荐