Postgres function: where clause aggregation with for loop

Question

I created a postgres function that receives an array of text as parameter. For each text in the array I want to check if any of the the columns in the query match it with like clause.

Is it possible to aggregate where clause like this in a for loop?

For example for clarification, I have the above tables:

lessons

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

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

I want to get as parameters text[] and for each text in the array I want to execute an OR clause between the column in both tables and return array of jsons([{"id": 1, "teacher": {"id":1, "name": "Teacher1"}}])

For example if the parameters are ["lish", "er"] I want it to execute:

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

teacher with id 1 will return.

The thing is I don't know in advance the parameters so this is why I assume I need a FOR loop.

Also, how to I make the query to return an array of jsons that each teacher will be an inner json object of each lesson?

Would appreciate some examples if so. Thank you!

Answer 1

You don't need a loop-like function such as in plpgsql to get your expected result. A basic sql query will be more efficient :

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)

• First this query joins both tables lessons and teachers with a match on teacher_id.
• Then it is joined with every element of the input array through the unnest function.
• For each row of the joined tables lessons and teachers, both names are compared to the input array element using the regular expression ~ operator.
• Then the selected rows are grouped together so that to count the number of rows which match with all the array elements. This check is performed in the HAVING clause where count(*) is the number of resulting rows for the same (lessons.id, teachers.id) tuple and it is compared to the total number of elements in the array with the array_length function..

If you want to return an array of jsonb, just format the query result :

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

see the full test result in dbfiddle