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!

your question is incomplete for a good understanding. Please update it with the query and its columns you talk about, and with an example of the result you want to achieve. — Edouard
– Edouard, Commented Nov 10, 2022 at 18:00
With examples it's hard to imagine what you need, but it's unlikely that you need a loop. — Frank Heikens
– Frank Heikens, Commented Nov 10, 2022 at 19:23

Edouard · Accepted Answer · 2022-11-11 16:53:26Z

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

edited Nov 11, 2022 at 16:53

answered Nov 10, 2022 at 20:28

Edouard

7,1291 gold badge11 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Tal Over a year ago

Wow, thank you so much. It works. Since this is a postgres function, what is the return type of it?

Edouard Over a year ago

the return type depends on the format of the columns in the SELECT clause. In the first query it is a record, in the second query it is a json type.

Tal Over a year ago

it seems that it doesn't make AND between the array values. Look at dbfiddle.uk/FJi54tW2

Edouard Over a year ago

The AND is performed through the HAVING clause which checks that the number of rows for a given tuple (lesson, teacher) and which match with the array elements equals the total number of elements in the array.

Tal Over a year ago

So why in the dbfiddle I sent it doesn’t work as expected ? It was supposed to return empty array because there is no ‘2’ in the teacher 1 with English name

|

Collectives™ on Stack Overflow

Postgres function: where clause aggregation with for loop

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related