Execute same query multiple times with different parameter postgressql

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

Answer

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

Advertisement

Answer