dtz63853 2017-04-29 21:20
浏览 376
已采纳

在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 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"})
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 python:excel数据写入多个对应word文档
  • ¥60 全一数分解素因子和素数循环节位数
  • ¥15 ffmpeg如何安装到虚拟环境
  • ¥188 寻找能做王者评分提取的
  • ¥15 matlab用simulink求解一个二阶微分方程,要求截图
  • ¥30 乘子法解约束最优化问题的matlab代码文件,最好有matlab代码文件
  • ¥15 写论文,需要数据支撑
  • ¥15 identifier of an instance of 类 was altered from xx to xx错误
  • ¥100 反编译微信小游戏求指导
  • ¥15 docker模式webrtc-streamer 无法播放公网rtsp