时间戳

首页 > 解决方案 > 使用函数更新 PostgreSQL 表中的数组

postgresql - 使用函数更新 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} |

我的代码有什么问题？

标签： postgresqlsql-update

解决方案

默认情况下，在 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_pa​​rt │ 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} │
└────────────────────┴────┴────┴────┴──────┴────────── ────┴──────────┴────────────────────┘</pre>


然而，更好的方法是创建更通用的功能，例如



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




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

推荐阅读