postgresql - 遍历 PostgreSQL 中的所有选定行

Question

我的问题是将我从选择中获得的所有行插入到表中。这是我的功能:

CREATE OR REPLACE FUNCTION imp_br_id_telefon_hlr_for_one_bank(id_bank bigint)
  RETURNS void AS
$BODY$
DECLARE
    v_record                            record;
    v_query                 text;

BEGIN
    FOR v_record IN 
    SELECT DISTINCT(bk_telefon.id) as id,
        coalesce(bk_telefon.nr_kierunkowy || nr_telefonu, nr_telefonu) as nr_telefon FROM bk_telefon
    INNER JOIN bp_dluznik2produkt ON bk_telefon.id_dluznik = bp_dluznik2produkt.id_dluznik 
    INNER JOIN bp_produkt ON bp_produkt.id = bp_dluznik2produkt.id_produkt 
    INNER JOIN d_bk_typ_telefon ON d_bk_typ_telefon.id = bk_telefon.id_typ_telefon 
    WHERE bp_produkt.id_bank IN(id_bank)
    AND d_bk_typ_telefon.id NOT IN (5, 11, 20, 21, 22) 
    AND bk_telefon.active=true 
    AND bk_telefon.nr_telefonu NOT IN ('000000', '111111', '222222', '333333', '444444', '555555', '666666', '777777', '888888','999999')
    AND bp_produkt.id_status_produkt NOT IN (10, 32, 33, 2)
    AND bk_telefon.data_ins::date <= '2016-12-08'
    LOOP
        INSERT INTO br_raport_telefonow(id_telefon, nr_telefonu, data_generacji) VALUES (v_record.id, v_record.nr_telefon, CURRENT_DATE);
        raise notice 'insertuje do weryfikacji';
    END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION imp_br_id_telefon_hlr_for_one_bank(bigint)
  OWNER TO mwalko;

运行此函数后，我在表 (br_raport_telefonow) 中得到 4453 行，但是当我运行查询时(这是函数的一部分):

SELECT DISTINCT(bk_telefon.id) as id,
        coalesce(bk_telefon.nr_kierunkowy || nr_telefonu, nr_telefonu) as nr_telefon FROM bk_telefon
    INNER JOIN bp_dluznik2produkt ON bk_telefon.id_dluznik = bp_dluznik2produkt.id_dluznik 
    INNER JOIN bp_produkt ON bp_produkt.id = bp_dluznik2produkt.id_produkt 
    INNER JOIN d_bk_typ_telefon ON d_bk_typ_telefon.id = bk_telefon.id_typ_telefon 
    WHERE bp_produkt.id_bank IN(id_bank)
    AND d_bk_typ_telefon.id NOT IN (5, 11, 20, 21, 22) 
    AND bk_telefon.active=true 
    AND bk_telefon.nr_telefonu NOT IN ('000000', '111111', '222222', '333333', '444444', '555555', '666666', '777777', '888888','999999')
    AND bp_produkt.id_status_produkt NOT IN (10, 32, 33, 2)
    AND bk_telefon.data_ins::date <= '2016-12-08'

它返回 426163 行。它不应该遍历 select 中的每一行吗？我不知道我的 42k 行在哪里丢失了。我错过了什么？

Answer 1

放弃循环并尝试

INSERT INTO br_raport_telefonow(id_telefon, nr_telefonu, data_generacji)

    SELECT DISTINCT(bk_telefon.id) as id,
        coalesce(bk_telefon.nr_kierunkowy || nr_telefonu, nr_telefonu) as nr_telefon, CURRENT_DATE
 FROM bk_telefon
    INNER JOIN bp_dluznik2produkt ON bk_telefon.id_dluznik = bp_dluznik2produkt.id_dluznik 
    INNER JOIN bp_produkt ON bp_produkt.id = bp_dluznik2produkt.id_produkt 
    INNER JOIN d_bk_typ_telefon ON d_bk_typ_telefon.id = bk_telefon.id_typ_telefon 
    WHERE bp_produkt.id_bank IN(id_bank)
    AND d_bk_typ_telefon.id NOT IN (5, 11, 20, 21, 22) 
    AND bk_telefon.active=true 
    AND bk_telefon.nr_telefonu NOT IN ('000000', '111111', '222222', '333333', '444444', '555555', '666666', '777777', '888888','999999')
    AND bp_produkt.id_status_produkt NOT IN (10, 32, 33, 2)
    AND bk_telefon.data_ins::date <= '2016-12-08'