Create table with for loop postgresql

Question

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

What is the source of your arguments? What do want in the end? An array? A table with one row per result? — S-Man
– S-Man, Commented Jul 1, 2019 at 9:12
Thank you for the quick response. my source of argument is going to be from a subquery. — Quy Vu Xuan
– Quy Vu Xuan, Commented Jul 1, 2019 at 9:13
the test_func() would returns multiple rows. So for example, each call of test_func() returns 10 rows, and if I have 2 inputs to the argument X, I would get 20 rows. I hope this is clear. — Quy Vu Xuan
– Quy Vu Xuan, Commented Jul 1, 2019 at 9:14

S-Man · Accepted Answer · 2019-07-01 09:30:15Z

1

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.

answered Jul 1, 2019 at 9:30

S-Man

24k9 gold badges51 silver badges78 bronze badges

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

2 Comments

user330315 Over a year ago

You don't need a sub-query there: ... from my_table, test_func(my_data) mf will work just fine

S-Man Over a year ago

@a_horse_with_no_name Yes, I just wanted to demonstrate the subquery point the TO made. He wrote that he was using a subquery.

score 1 · Accepted Answer · 2019-07-01 09:48:54Z

1

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;

edited Jul 1, 2019 at 9:48

answered Jul 1, 2019 at 9:29

user330315

2 Comments

Quy Vu Xuan Over a year ago

It throws a "syntax error at the end of input" message.

user330315 Over a year ago

@QuyVuXuan: ah, sorry. Fixed the typo

Collectives™ on Stack Overflow

Create table with for loop postgresql

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related