sql - 带光标的 Redshift 存储过程循环

Question

我正在尝试遍历表的所有行t1并在将行插入新表之前逐行应用一些逻辑member_visits：

as
$$
DECLARE

    cur cursor for
    select prs_nat_key,pos_cd,pos_nm, serv_from_dt, serv_to_dt
    from t1
    order by prs_nat_key, serv_from_dt, serv_to_dt;

    counter int;

    prs_nat_key varchar;
    pos_cd varchar;
    pos_nm varchar;
    serv_from_date date;
    serv_to_date date;

BEGIN
    counter = 1;
    drop table if exists member_visits;
    CREATE TEMP TABLE member_visits(prs_nat_key varchar, pos_cd varchar, pos_nm varchar,
                                    serv_from_date varchar, serv_to_date varchar, visit_index int);


WHILE cur: -- loop through cursor until no more rows in table to loop through
    (prs_nat_key, pos_cd, pos_nm, serv_from_date, serv_to_date) = fetch next from cur; -- assign variables the values of the next iteration

/* apply some logic here that adjusts the counter variable, serv_from_date, serv_to_date based on values from the cursor  and exisiting values within member_visits*/

    INSERT member_visits VALUES (prs_nat_key, pos_cd, pos_nm, serv_from_date, serv_to_date, counter);

--maybe end while loop?
END;
$$
language plpgsql;

关于如何做到这一点的任何建议？

标签： sqlamazon-redshift