在PostgreSQL中,是否可以省略WHERE子句中的字段?

huangapple go评论129阅读模式
英文:

Is it possible to omit field in where clause in postgresql?

问题

在SQL中,我有以下代码:

  1. -- name: FilterRecords :many
  2. SELECT *
  3. FROM records
  4. WHERE industry_id = $3 and region_code = $4 and city_code = $5
  5. OFFSET $1 LIMIT $2;

我想要实现的目标是,如果提供的 industry_id 值为空字符串,则在 WHERE 子句中排除它。因为当我进行查询时,它会计算空字符串,但我希望如果它为空并且只选择与 region_code 和 city_code 匹配的记录。

英文:

In SQL I have following code:

  1. -- name: FilterRecords :many
  2. SELECT *
  3. FROM records
  4. WHERE industry_id = $3 and region_code = $4 and city_code = $5
  5. OFFSET $1 LIMIT $2;

What I'm trying to achieve is to exclude industry_id from where clause if provided value for it is blank string. Because when I'm making query it's counting blank strings, but I want to omit it if it's blank and select only records where they match region_code and city_code.

答案1

得分: 0

你可以使用Golang的默认text/template来实现这个。

  1. variables := map[string]interface{}{
  2. "industry_id": ...,
  3. "region_id": ...,
  4. "city_code": ...,
  5. "offset": ...,
  6. "limit": ...,
  7. }
  8. qs := new(strings.Builder)
  9. template.Must(template.New("qt").Parse(`
  10. SELECT *
  11. FROM records where
  12. {{ if .industry_id -}} industry_id = {{.industry_id}} and {{- end }}
  13. region_code = {{.region_code}} and city_code = {{.city_code}}
  14. OFFSET {{.offset}} LIMIT {{.limit}}`)).Execute(qs, variables)
  15. fmt.Printf("qs: %v\n", qs.String())
英文:

You can do this by golangs default text/template.

  1. variables := map[string]interface{} {
  2. "industry_id": ...,
  3. "region_id": ...,
  4. "city_code": ...,
  5. "offset": ...,
  6. "limit": ...,
  7. }
  8. qs := new(strings.Builder)
  9. template.Must(template.New("qt").Parse(`
  10. SELECT *
  11. FROM records where
  12. {{ if .industry_id-}} industry_id= {{.industry_id}} and {{- end }}
  13. region_code = {{.region_code}} and city_code = {{.city_code}}
  14. OFFSET {{.offset}} LIMIT {{.limit}}`)).
  15. Execute(qs, variables)
  16. fmt.Printf("qs: %v\n", qs.String())

答案2

得分: 0

这是为我定制的一个并且正常工作的。

  1. -- 名称:FilterApz:多个
  2. 选择 *
  3. apz
  4. 其中 industry_id LIKE COALESCE(NULLIF($3, ''), '%%') and region_code = $4 and city_code = $5
  5. 偏移 $1 限制 $2;
英文:

This is the one made for me and worked correctly.

  1. -- name: FilterApz :many
  2. SELECT *
  3. FROM apz
  4. WHERE industry_id LIKE COALESCE(NULLIF($3, ''), '%%') and region_code = $4 and city_code = $5
  5. OFFSET $1 LIMIT $2;

huangapple
  • 本文由 发表于 2023年7月24日 20:12:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/76754387.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定