Using a PostgreSQL function inside a loop-CodePudding

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, '[email protected]')

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 "[email protected]"

CodePudding user response：

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

SELECT * FROM (select my_function(id, '[email protected]') from mytable);

CodePudding user response：

You can use a cross join:

SELECT * 
FROM my_table mt 
  cross join lateral my_function(mt.id, '[email protected]') as mf