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

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

图片转代码服务由CSDN问答提供 功能建议

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

 创建或替换功能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; 
   
  
 

所以我可以运行像这样的多态查询:

  SELECT read_user(_id:= 2); 
SELECT read_user(_fir  st:='John',_last:='Doe'); 
   
 
 

在Golang中,我可以做类似的事情:

  stmt,err:= db.Prepare(“ SELECT read_user(_id =?)”“ 
   
 
 

但是我该怎么做,但是 read_user 参数的数量可变? 我正在使用 pq 驱动程序 https://github.com/lib/pq

  • 写回答
  • 好问题 提建议
  • 追加酬金
  • 关注问题
  • 邀请回答

1条回答 默认 最新

相关推荐 更多相似问题