Execute same query multiple times with different parameter postgressql

Question

I don't know how to easily explain what i need to do but i'll try.

Lets say i have a user table with 5 users.

id   name
1    Steve
2    Pat
3    Robin
4    Carl
5    Sarah

instead of just do a select * from users i need to do this different and more difficult.

I need to build a query that for each row in users table runs a query with a parameter (name) and then gives me the same output as select * from users

I know it's sounds wierd but this is what i actually needs to do..

So what i want to happen is this:

I need to loop thru the users table to find out how many rows there is. (5) -That's the amount of times I want to execute the query.
For each execution of the Query i need to change name in the where clause. First execution = Steve, second = Pat and so on.
In the end I want just one output with everything together so I need to union the result.

If i would this manually it would look like this:

Select id, name from users where name = 'Steve'
union all 
Select id, name from users where name = 'Pat'
union all
Select id, name from users where name = 'Robin'
union all 
Select id, name from users where name = 'Carl'
union all
Select id, name from users where name = 'Sarah'

In my real case i need separate queries so a in ('Steve', 'Pat') or a solution like that won't work.

I hope you understand what im looking for, and if you have any question please ask. Im using postgres v.10

where name in ('Steve', 'Pat', ...)

jarlh
– jarlh

2022-03-04 10:12:45 +00:00
Commented Mar 4, 2022 at 10:12 — jarlh
– jarlh, Commented Mar 4, 2022 at 10:12
In my real case that won't work. I need separate queries

user2210516
– user2210516

2022-03-04 10:24:16 +00:00
Commented Mar 4, 2022 at 10:24 — user2210516
– user2210516, Commented Mar 4, 2022 at 10:24

Eagle74 · Accepted Answer · 2022-03-04 14:34:36Z

1

This should work as you intend to.

DO $$

DECLARE
    var_req TEXT;

    rec_key record;
    cur_key CURSOR FOR SELECT distinct name from users;

BEGIN

    open cur_key;

    
    loop
        fetch cur_key into rec_key;

        EXIT WHEN NOT FOUND;
var_req := '
insert into your_output_table
    select id, name from users
    where name = '''||rec_key.name||'''
;
';

execute var_req;    
        
    end loop;
    
    close cur_key;

END $$;

answered Mar 4, 2022 at 14:34

Eagle74

363 bronze badges

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

1 Comment

Community Over a year ago

Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Execute same query multiple times with different parameter postgressql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related