function - Postgresql 9.2 触发分隔存储字符串中的子字段

Question

Postgresql 9.2 数据库，自动从各种机器收集数据。数据库存储所有数据，包括机器 ID、固件、制造商 ID 等以及实际结果数据。在一个存储字段 (varchar) 中有 5 个子字段，它们由 ^ 字符分隔。

ACT18!!!8246-EN-2.00013151!1^7.00^F5260046959^H1P1O1R1C1Q1L1^1 (Machine 1)

此数据的顺序似乎因一台机器而异。例如机器 1 2 和 3。上面的字符串显示固件版本，在本例中为“7.0”，它出现在子字段 2 中。但是，另一台机器在不同的子字段中发送数据 - 在本例中为子字段3，值为“1”

BACT/ALERT^A.00^1^^ (Machine 2)

我想使用 CREATE TRIGGER t_machine_id AFTER INSERT 函数将值“7.0”和“1”存储在单独表的不同字段中，我可以根据数据来自的机器选择使用哪个子字段来自。

split_part 是执行此操作的最佳函数吗？任何人都可以提供一个示例代码来执行此操作吗？我在文档中找不到任何内容。

Answer 1

您需要 (a) 使用类似 regexp_split_to_table 的方式拆分数据然后 (b) 使用某些标准匹配哪些部分是哪些部分，因为您没有可依赖的字段位置顺序。现在我没有看到任何可靠的规则来决定什么是固件版本和什么是机器号；你真的不能说where field <> machine_number因为如果机器 1 的固件版本为 1你不会得到任何结果。

给定虚拟数据:

CREATE TABLE machine_info(data text, machine_no integer);
INSERT INTO machine_info(data,machine_no) (VALUES
  ('ACT18!!!8246-EN-2.00013151!1^7.00^F5260046959^H1P1O1R1C1Q1L1^1',1),
  ('BACT/ALERT^A.00^1^^',2)
);

类似于:

SELECT machine_no, regexp_split_to_table(data,'\^') 
FROM machine_info;

会给你一个带有机器号的拆分数据元素表，但是你需要决定哪些字段是哪些:

 machine_no |    regexp_split_to_table     
------------+------------------------------
          1 | ACT18!!!8246-EN-2.00013151!1
          1 | 7.00
          1 | F5260046959
          1 | H1P1O1R1C1Q1L1
          1 | 1
          2 | BACT/ALERT
          2 | A.00
          2 | 1
          2 | 
          2 | 
(10 rows)

您可能会发现替换 regexp_split_to_array 的输出更有用，这取决于您是否可以从字段顺序中获取任何有用的信息以及您打算如何处理数据。

regress=# SELECT machine_no, regexp_split_to_array(data,'\^') 
          FROM machine_info;
 machine_no |                      regexp_split_to_array                       
------------+------------------------------------------------------------------
          1 | {ACT18!!!8246-EN-2.00013151!1,7.00,F5260046959,H1P1O1R1C1Q1L1,1}
          2 | {BACT/ALERT,A.00,1,"",""}
(2 rows)

假设有两个固件版本；版本 1 发送 code^blah^fwvers^^版本 2 和更高版本发送 code^fwvers^blah^blah2^machineno .然后您可以区分两者，因为您知道版本 1 将最后两个字段留空:

SELECT
  machine_no,
  CASE WHEN info_arr[4:5] = ARRAY['',''] THEN info_arr[3] ELSE info_arr[2] END AS fw_vers
FROM (
  SELECT machine_no, regexp_split_to_array(data,'\^')
  FROM machine_info
) string_parts(machine_no, info_arr);

结果:

 machine_no | fw_vers 
------------+---------
          1 | 7.00
          2 | 1
(2 rows)

当然，你只是提供了两个样本数据，所以真正的匹配规则可能会更复杂。考虑编写一个 SQL 函数来提取所需的字段并从传递的数组中返回它们。