CODEWITHSUNDEEP
postgresqlfunctionloopstransactions
gwydion93
gwydion93

Reputation: 1923

Using a PostgreSQL function inside a loop

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]"

Upvotes: 0

Views: 113

Answers (2)

user330315
user330315

Reputation:

You can use a cross join:

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

Upvotes: 1

GabrielNexT
GabrielNexT

Reputation: 364

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);

Upvotes: 1

Related Questions