current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have this snippet of code:

RAISE NOTICE 'p_new_roster: %', p_new_roster; --prints {3,4,5}
FOREACH i IN ARRAY p_new_roster
LOOP
  RAISE NOTICE 'p_new_roster[i]: %', p_new_roster[i]; --prints 5,NULL,NULL (through 3 iterations)
  v_new_users := v_new_users || p_new_roster[i];
END LOOP;

How come p_new_roster[1] is 5, p_new_roster[2] is NULL, etc.? Variable i is declared as integer, and arrays are declared as integer[].

Basically what I am trying to do is clone 'p_new_roster' array. If somebody knows better way, I would like to know how too.

share|improve this question
1  
PostgreSQL arrays are immutable. Why would you "clone" it? Just v_new_users = p_new_roster. What's the actual problem you're trying to solve by doing this, the "why" for the "how" you're asking here? –  Craig Ringer 2 days ago
    
I have two arrays: arr1 = {1,2,3} and arr2 = {3,4,5} (this is just example). I need function that compares these two arrays and returns in one column {1,2} and in second {4,5} (I would put the result in composite type). –  anagarD 2 days ago
    
If I do v_new_users := p_new_roster then changes that I do with v_new_users would be seen in p_new_roster also? I need p_new_roster for later use. –  anagarD 2 days ago
    
@anagarD No. You assign the value of p_new to v_new not a reference to p_new –  Clodoaldo Neto 2 days ago
    
No, you've completely misunderstood how arrays work in PostgreSQL. They're immutable. It's not like Java where assigning the array just assigns the reference. The whole array is copied. It's like a string in Java. You can't modify an array in-place by setting attributes. –  Craig Ringer 2 days ago
add comment

1 Answer

up vote 0 down vote

If you are trying to take the nulls out of the array do

v_new_users := v_new_users || array_remove(p_new_roster, null)

without the loop

Or just append

v_new_users := v_new_users || p_new_roster

also without the loop

Or your script fixed

RAISE NOTICE 'p_new_roster: %', p_new_roster; --prints {3,4,5}
FOREACH i IN ARRAY p_new_roster
LOOP
  RAISE NOTICE 'p_new_roster[i]: %', i; 
  v_new_users := v_new_users || i;
END LOOP;

i is the array element value at each iteration not the array index

share|improve this answer
    
I tought i was index -.- Thanks your answer helped a lot! –  anagarD 2 days ago
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.