Подтвердить что ты не робот

Обновите массив в таблице PostgreSQL, используя функцию

Я пытаюсь обновить таблицу PostgreSQL с помощью функции.

Моя функция:

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
;

Для вызова моей функции:

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

Проблема в том, что когда я делаю простой выбор в моей командной строке PostgreSQL, данные из обновления null.

Мой выбор:

SELECT * FROM tab_files;

Выход:

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

Что случилось с моим кодом?

Ответ 1

В столбце PostgreSQL индекс по умолчанию начинается с 1. Таким образом, $2[k]= $2[0] (из-за k := 0;) = null. То же самое с $3[k].

Также не рекомендуется обновлять одну и ту же строку в цикле несколько раз. Лучший способ - выбрать значения полей в локальные переменные, изменить их и затем обновить таблицу один раз.

Обновление. Если я правильно догадываюсь о цели функции, можно было бы упростить обновление столбцов одним шагом без цикла:

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;

Вы можете поэкспериментировать с этим простым примером:

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;

Результат:

┌───────────────────┬───┬───┬───┬──────┬──────────────┬─────────┬───────────────────┐
│         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} │
└───────────────────┴───┴───┴───┴──────┴──────────────┴─────────┴───────────────────┘

Однако лучший способ - создать более общую функцию, такую как

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:]
$$;

а затем использовать его в своем обновлении:

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';

И, конечно же, вы сможете повторно использовать эту функцию в других задачах.