dtz63853
dtz63853
2017-04-29 21:20
浏览 189

在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.

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • duandoucou7200
    duandoucou7200 2017-04-29 22:09
    已采纳

    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"})
    
    点赞 评论

相关推荐