Using a PostgreSQL function inside a loop

gwydion93 0
Viewed 10

Suppose I have a PostgreSQL function that takes 2 parameters: id (INT), email (TEXT) and can be called like this:

SELECT * FROM my_function(101, 'myemail@gmail.com')

I want to run a SELECT query from a table that would return multiple id's:

SELECT id FROM mytable

  | id   |
--+------+
  | 101  |
--+------+
  | 102  |
--+------+
  | 103  |

How would I loop through and plug each of the returned id's into my function in a query. FOr this example just assume the default email is alwasy "myemail@gmail.com"

transactions function loops postgresql
2 Answers

I'm on mobile so I can't test it, but I think maybe this will work.

SELECT * FROM (select my_function(id, 'myemail@gmail.com') from mytable);
GabrielNexT0

You can use a cross join:

SELECT * 
FROM my_table mt 
  cross join lateral my_function(mt.id, 'myemail@gmail.com') as mf
a_horse_with_no_name0
Related