sql - 重新编号文本列中的引用变量

Question

背景

对于数据输入项目，用户可以使用速记符号输入变量:

"Pour i1 into a flask."
"Warm the flask to 25 degrees C."
"Add 1 drop of i2 to the flask."
"Immediately seek cover."

在这种情况下，i1 和 i2 是引用变量，其中数字指的是一种成分。文本字符串在 INSTRUCTION 表中，成分在 INGREDIENT 表中。

每种成分都有一个用于分类的序列号。

问题

用户可能会重新排列成分顺序，这会对说明产生不利影响。例如，最初的配料顺序可能如下所示:

seq | label
1   | water
2   | sodium

用户添加了另一种成分:

seq | label
1   | water
2   | sodium
3   | francium

用户重新排序列表:

seq | label
1   | water
2   | francium
3   | sodium

此时，以下行现在是不正确的:

"Add 1 drop of i2 to the flask."

i2 必须重新编号(因为成分 #2 已移至位置 #3)以指向原始引用变量:

"Add 1 drop of i3 to the flask."

更新详情

这是问题的简化版本。完整的问题可以包含如下行:

"Add 1 drop of i2 to the o3 of i1."

其中o3是一个物体( flask )，i1和i2分别是水和钠。

表结构

成分表的结构如下:

id | seq | label

指令表结构如下:

step

算法

我想到的算法:

1. 对所有匹配表达式 '\mi([0-9]+)' 的 step 重复:
2. 将步骤分解为词标记。
3. 对于每个 token :
   1. 如果 token 的数字部分与旧序列号匹配，则将其替换为新序列号。
   2. 重新组合标记并更新指令。
   3. 更新成分编号。

更新

算法可能不正确。可能有两个必须更改的引用变量。考虑之前:

seq | label
1   | water
2   | sodium
3   | caesium
4   | francium

之后(交换钠和铯):

seq | label
1   | water
2   | caesium
3   | sodium
4   | francium

每一步的i2都必须变成i3；同样，i3 必须变为 i2。所以

"Add 1 drop of i2 to the flask, but absolutely do not add i3."

变成:

"Add 1 drop of i3 to the flask, but absolutely do not add i2."

代码

执行算法前两部分的代码类似于:

CREATE OR REPLACE FUNCTION
  renumber_steps(
    p_ingredient_id integer,
    p_old_sequence integer,
    p_new_sequence integer )
  RETURNS void AS
$BODY$
DECLARE
  v_tokens text[];

BEGIN
  FOR v_tokens IN
    SELECT
      t.tokens
    FROM (
      SELECT
        regexp_split_to_array( step, '\W' ) tokens,
        regexp_matches( step, '\mi([0-9]+)' ) matches
      FROM
        instruction
      ) t
  LOOP
    RAISE NOTICE '%', v_tokens;
  END LOOP;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

问题

解决此问题的更有效方法是什么(即，您将如何消除循环结构)，可能利用 PostgreSQL 特定的功能，而无需对数据模型进行重大修改？

谢谢!

系统详情

PostgreSQL 9.1.2。

Answer 1

您必须注意不要来回更改成分和 seq 编号。为此，我为 seq 的成分和负数引入了一个临时前缀，并在所有完成后将它们交换为永久值。

可以这样工作:

CREATE OR REPLACE FUNCTION renumber_steps(_old int[], _new int[])
  RETURNS void AS
$BODY$
DECLARE
    _prefix     CONSTANT text := ' i';    -- prefix, incl. leading space
    _new_prefix CONSTANT text := ' ###';  -- temp prefix, incl. leading space
    i           int;
    o           text;
    n           text;
BEGIN

IF array_upper(_old,1) <> array_upper(_new,1) THEN
    RAISE EXCEPTION 'Array length mismatch!';
END IF;

FOR i IN 1  .. array_upper(_old,1) LOOP
    IF _old[i] <> _new[i] THEN
        o := _prefix || _old[i] || ' ';        -- leading and trailing blank!
        -- new instruction are temporarily prefixed with new_marker
        n := _new_prefix || _new[i]  || ' ';

        UPDATE instruction
        SET    step = replace(step, o, n)      -- replace all instances
        WHERE  step ~~ ('%' || o || '%');

        UPDATE ingredient
        SET    seq = _new[i] * -1              -- temporarily negative
        WHERE  seq = _old[i];
    END IF;
END LOOP;

-- finally replace temp. prefix
UPDATE instruction
SET    step = replace(step, _new_prefix, _prefix)
WHERE  step ~~ ('%' || _new_prefix || '%');

-- .. and temp. negative seq numbers
UPDATE ingredient
SET    seq = seq * -1
WHERE  seq < 0;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE STRICT;

调用:

SELECT renumber_steps('{2,3,4}'::int[], '{4,3,2}'::int[]);

算法需要...
...步骤中的成分由空格分隔。
...没有永久的负序列号。

_old 和 _new 是改变位置的成分的新旧 instruction.seq 的数组。两个数组的长度必须匹配，否则将引发异常。它可以包含不变的 seq。这些都不会发生。

需要 PostgreSQL 9.1 或更高版本。