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