postgresql - 使用函数更新 PostgreSQL 表中的数组

Question

我正在尝试使用函数更新 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} |

我的代码有什么问题？

Answer 1

在 PostgreSQL 中，数组索引默认从 1 开始。因此 $2[k] = $2[0](因为 k := 0;) = 空。与 $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} │└───────────────────┴───┴───┴───┴──────┴──────────────┴─────────┴───────────────────┘

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

然后在您的更新中使用它:

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

当然，您将能够在其他任务中重用此功能。