postgresql Postgres函数：使用for循环进行Where子句聚合

0sgqnhkj 于 2022-11-23 发布在 PostgreSQL

关注(0)|答案(1)|浏览(237)

我创建了一个postgres函数，它接收一个文本数组作为参数。对于数组中的每一个文本，我想检查查询中是否有列与like子句匹配。
在for循环中可以像这样聚合where子句吗？
例如为了澄清，我有上面的表格：

lessons

+----+-----------+-----------------+
| id | name      | teacher_id      |
+----+-----------+-----------------+
| 1  | English   | 1               |
+----+-----------+-----------------+
| 2  | Spanish   | 2               |
+----+-----------+-----------------+
Teachers

+----+-----------+
| id | name      |
+----+-----------+
| 1  | Teacher 1 |
+----+-----------+
| 2  | Teacher 2 |
+----+-----------+

我想获取text[]作为参数，对于数组中的每个文本，我想在两个表中的列之间执行OR子句，并返回jsons数组（[{"id": 1, "teacher": {"id":1, "name": "Teacher1"}}]）
例如，如果参数是[“lish”，“er”]，我希望它执行：

where 
   lessons.name like '%lish%' or teachers.name like '%lish%'
and
   lessons.name like '%er%' or teachers.name like '%er%'

将返回ID为1的教师。
问题是我事先不知道参数，所以这就是为什么我假设我需要一个FOR循环。
另外，我如何进行查询以返回一个json数组，使每个教师都成为每节课的内部json对象？
如果是的话，会很感激一些例子。谢谢！

postgresql

来源：https://stackoverflow.com/questions/74392789/postgres-function-where-clause-aggregation-with-for-loop

1条答案

按热度按时间

nhn9ugyo1#

你不需要像plpgsql那样的循环函数来得到你想要的结果。一个基本的sql查询会更有效率：

SELECT l.id, l.name, t.id, t.name
  FROM lessons AS l
 INNER JOIN teachers AS t
    ON t.id = l.teacher_id
 INNER JOIN unnest( array['lish', 'er']) AS c(cond)
    ON t.name ~ c.cond OR l.name ~ c.cond
 GROUP BY l.id, l.name, t.id, t.name
HAVING count(*) = array_length(array['lish','er'], 1)

首先，此查询将表lessons和teachers与teacher_id上的匹配项联接起来。
然后通过unnest函数将其与输入数组的每个元素连接。
对于联接表lessons和teachers的每一行，使用正则表达式~运算符将这两个名称与输入数组元素进行比较。
然后将选定的行分组在一起，以便计算与所有数组元素匹配的行数。此检查在HAVING子句中执行，其中count(*)是同一（lessons.id，www.example.com）元组的结果行数teachers.id，并使用array_length函数将其与数组中的元素总数进行比较。

如果要返回jsonb的数组，只需格式化查询结果即可：

SELECT json_agg(c.result)
  FROM (
SELECT (json_build_object('lesson_id', l.id, 'lesson_name',l.name, 'teacher', json_build_object('teacher_id', t.id, 'teacher_name', t.name))) AS result
  FROM lessons AS l
 INNER JOIN teachers AS t
    ON t.id = l.teacher_id
 INNER JOIN unnest( array['lish', 'er']) AS c(cond)
    ON t.name ~ c.cond OR l.name ~ c.cond
 GROUP BY l.id, l.name, t.id, t.name
HAVING count(*) = array_length(array['lish','er'], 1)) AS c

请参阅dbfiddle中的完整测试结果

赞(0）回复(0）举报 2022-11-23

我来回答

postgresql Postgres函数：使用for循环进行Where子句聚合

1条答案

相关问题

热门标签

最新问答