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
CodePudding user response:
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 $$;