CODEWITHSUNDEEP
postgresqlfunctionloops
Quy Vu Xuan
Quy Vu Xuan

Reputation: 189

Create table with for loop postgresql

I have a function test_func() that takes in 1 argument (let's say the argument name is X) and returns a table. Now, I have a list of inputs (from a subquery) that I want to pass into argument X and collect all the results of the calls in a table.

In Python, I would do something like 

# create empty list
all_results = []

for argument in (1,2,3):
    result = test_func(argument)

# Collect the result
all_results.append(result)

return all_results

How can I do the same thing in postgresql?

Thank you.

For the sake of example, my test_func(X) takes in 1 argument and spits out a table with 3 columns. The values for col1 is X, col2 is X+1 and col3 is X+3. For example:

select * from test_func(1)

gives

|col1|col2|col3|
----------------
| 1  | 2  | 3  |
----------------

My list of arguments would be results of a subquery, for example:

select * from (values (1), (2)) x

I expect something like:

|col1|col2|col3|
----------------
| 1  | 2  | 3  |
----------------
| 2  | 3  | 4  |
----------------

Upvotes: 0

Views: 198

Answers (2)

user330315
user330315

Reputation:

You can join your function to the input values: 

select f.* 
from (
  values (1), (2)
) as x(id) 
   cross join lateral test_func(x.id) as f;

Upvotes: 1

S-Man
S-Man

Reputation: 23676

demo:db<>fiddle

This gives you a result list of all results:

SELECT 
    mt.my_data as input,
    tf.*
FROM
    (SELECT * FROM my_table) mt,  -- input data from a subquery
    test_func(my_data) tf         -- calling every data set as argument

In the fiddle the test_func() gets an integer and generates rows (input argument = generated row count). Furthermore, it adds a text column. For all inputs all generated records are unioned into one result set.

Upvotes: 1

Related Questions