concat to array while looping in postgres

Question

I have an array of strings and I need to loop through the array; while looping for each string, I need to loop up the ID of that string in a table and create an INT array of those IDs.

Effectively, I need to go FROM:

{"a", "b", "c"}

TO:

{3,6,8}

I am getting this loop going with no errors, and I get the correct ID for each string in the original array, but I have an empty array declared which does not seem to get populated... This is the code

First raise notive gives me the correct privilege_group_id, but the second raise notice still returns an empty array.. so no concat happens. I tried ARRAY_APPEND() with the same results, always an empty array.

Any thoughts on what I am doing wrong?

Why a loop at all? Looks like you could simplify the complete thing to select array_agg(privilege_group_id FROM privilege_group WHERE privilege_group_code = any(a_existing_user_group_codes) — user330315
– user330315, Commented Apr 10, 2020 at 23:19
because I'm really bad at SQL :-) . You're right, your statement is much simpler — kravb
– kravb, Commented Apr 11, 2020 at 1:36

kravb · Accepted Answer · 2020-04-10 22:59:04Z

0

I found the answer here: array_append function is not working

You have to use SELECT instead of PERFORM and then INTO the same array

SELECT  ARRAY_APPEND(a_groups_to_int_array, v_single_exisisting_user_code) INTO a_groups_to_int_array;

answered Apr 10, 2020 at 22:59

kravb

5881 gold badge8 silver badges22 bronze badges

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

Collectives™ on Stack Overflow

concat to array while looping in postgres

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related