Update an array in a PostgreSQL table using a function

Question

I'm trying to update a PostgreSQL table using a function.

My function:

CREATE OR REPLACE FUNCTION update_array_words(varchar, varchar[], int[], int, int)
RETURNS int AS $$

DECLARE
  passed int;
  j int;
  k int;

BEGIN
  passed := 0;
  j := $4 + $5;
  k := 0;
  FOR i IN $4..j LOOP
    UPDATE tab_files
    SET key_words[i] = $2[k], num_key_words[i] = $3[k]
    WHERE path_to_file = $1;
  END LOOP;

  RETURN passed;
END;

$$
LANGUAGE plpgsql
;

For calling my function:

 SELECT update_array_words('path_name_to_file', '{"susana"}', '{1}', 1, 1);

The problem is, when I do a simple select in my PostgreSQL command line, the data from the update is null.

My select:

SELECT * FROM tab_files;

Output:

          key_words                      num_key_words
| [0:2]={marques,NULL,NULL} |        | [0:2]={3,NULL,NULL} |

What's wrong with my code?

Answer 1

In PostgreSQL arrays index by default starts from 1. Thus $2[k] = $2[0] (because of k := 0;) = null. Same with $3[k].

It is also not good idea to update same row in the loop several times. The better way is to select fields values into local variables, change them and then update your table once.

Update: If I guessing correctly about the purpose of the function, it could be simplified to update columns in single step without loop:

UPDATE tab_files set
  key_words = key_words[1:$4-1] || array_fill($2[k],array[$5-$4+1]) || key_words[$5+1:],
  num_key_words = num_key_words[1:$4-1] || array_fill($3[k],array[$5-$4+1]) || num_key_words[$5+1:]
WHERE path_to_file = $1;

You can to experiment with this using simple example:

with t(x,s,e,v) as (values(array[1,2,3,4,5,6,7,8], 2, 5, 0))
select
  *,
  x[1:s-1] as head,
  array_fill(v, array[e-s+1]) as changed_part,
  x[e+1:] as tail,
  x[1:s-1] || array_fill(v, array[e-s+1]) || x[e+1:] as final_result
from t;

Result:

┌───────────────────┬───┬───┬───┬──────┬──────────────┬─────────┬───────────────────┐
│         x         │ s │ e │ v │ head │ changed_part │  tail   │   final_result    │
├───────────────────┼───┼───┼───┼──────┼──────────────┼─────────┼───────────────────┤
│ {1,2,3,4,5,6,7,8} │ 2 │ 5 │ 0 │ {1}  │ {0,0,0,0}    │ {6,7,8} │ {1,0,0,0,0,6,7,8} │
└───────────────────┴───┴───┴───┴──────┴──────────────┴─────────┴───────────────────┘

However the better way is to create more general function like

create function array_replace_series(
  p_array anyarray,
  p_value anyelement,
  p_start int,
  p_end int)
returns anyarray language sql immutable
as $$
  select
    p_array[1:p_start-1] ||
    array_fill(p_value, array[p_end-p_start+1]) ||
    p_array[p_end+1:]
$$;

and then use it in your update:

UPDATE tab_files set
  key_words = array_replace_series(key_words, 'susana', 1, 1),
  num_key_words = array_replace_series(num_key_words, 1, 1, 1)
WHERE path_to_file = 'path_name_to_file';

And of course you will be able to reuse this function in other tasks.