Loop over all selected rows in PostgreSQL

Question

My problem is inserting to table all rows which I got from select. Here is my function:

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;

After running this function I got 4453 rows in table (br_raport_telefonow) but when I just run query (which is part of function):

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'

it returns 426163 rows. Shouldn't it loop over every row from select? I have no idea where my 42k rows have lost. What am I missing?

I don't think you need a loop or cursor. Just plain SQL could do the same. — joop
– joop, Commented Dec 19, 2016 at 10:15
argument when you run imp_br_id_telefon_hlr_for_one_bank?.. and when yourun SQL statement, you can't use bp_produkt.id_bank IN(id_bank) - what were real values? — Vao Tsun
– Vao Tsun, Commented Dec 19, 2016 at 10:15
It's function for one use. I run it with SELECT * FROM imp_br_id_telefon_hlr_for_one_bank(5) but of course can delete argument and add 5 as id_bank — Michu93
– Michu93, Commented Dec 19, 2016 at 10:17
Unrelated, but: distinct is NOT a function. It always applies to all columns in the select list. Writing DISTINCT(bk_telefon.id) is useless. Did you mean to use distinct on () instead? — user330315
– user330315, Commented Dec 19, 2016 at 10:22

e4c5 · Accepted Answer · 2016-12-19 10:18:42Z

1

Ditch the loop and try

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'

answered Dec 19, 2016 at 10:18

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Michu93 Over a year ago

That works! Thanks but still don't know why mine function didn't

e4c5 Over a year ago

I don't know either but let's not worry about it! :) Postgresql is fantastically good at big queries this will run much faster than looping over each row and inserting separately. Anyway, glad to have helped. All the best with your project.

Collectives™ on Stack Overflow

Loop over all selected rows in PostgreSQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related