Using a PostgreSQL function inside a loop

Question

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

GabrielNexT · Accepted Answer · 2022-09-20 19:52:41Z

1

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

answered Sep 20, 2022 at 19:52

GabrielNexT

3643 silver badges13 bronze badges

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

Comments

user330315 · Accepted Answer · 2022-09-20 19:55:55Z

1

You can use a cross join:

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

answered Sep 20, 2022 at 19:55

user330315

Collectives™ on Stack Overflow

Using a PostgreSQL function inside a loop

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related