嵌套While循环在红移

Question

我的表是这样的

Parent_Id   Child_Id    Product Prod_count
1000          1            A    1
1000          2          A+B    1
1000          3            A    1
1000          4          B+C    1
2000          1            A    1
2000          2          B+C    1
2000          3            C    1
2000          4            D    1

我试图在这个过程中做嵌套循环，循环基于Parent_Id，每个父有不同的子，所以也必须读取每一行。

我已经试过了

create or replace procedure sp_dummy(IN var1 int, IN var2 int, IN var3 int) as $$
Begin
create temp table find_id as(  
select distinct parent_id,row_number() over(order by 1) as rw_num
from table_1
);

declare 
tot_cnt int := (select count(distinct parent_id) from find_id );
init_loop int := 1;
in_init_loop int := 1;
in_tot_init_loop int;
v_parent_id int;
Begin
While init_loop <= tot_cnt    
Loop
Raise info 'init_loop = %', Init_loop;  
Execute 'Select parent_id into ' || v_parent_id  || ' from find_id where rw_num = ' || Init_loop;
Raise info 'v_patient_id = %', v_patient_id;

Execute 'Select Count(*) into ' || in_tot_init_loop  || ' from Table_1 where Parent_Id = ' || v_parent_id;

While in_init_loop <= in_tot_init_loop
Loop
Raise info 'in_init_loop = %', in_init_loop;
in_init_loop = in_init_loop + 1
End loop;

init_loop = init_loop + 1;
end loop;     

End; 
End;
$$ language plpgsql;

在尝试这个我得到错误不能执行空查询字符串我放弃了尝试理解这个错误!!(

Answer 1

这行似乎有问题:

Execute 'Select parent_id into ' || v_parent_id  || ' from find_id where rw_num = ' || Init_loop;

v_parent_id为空，因此它将转换为:

Select parent_id into NULL from find_id where rw_num = 1;

我想你实际上想写的是:

SELECT INTO v_parent_id
parent_id
FROM find_id
WHERE rw_num = Init_loop;

是的，你实际上可以将SQL内嵌，而不必将其作为字符串传递给EXECUTE。看一下PL/pgSQL的结构- Amazon Redshift的例子。