主页 / dba / 问题 / 136675
Accepted
user83914
Asked: 2016-04-27 14:37:41 +0800 CST

在 PostgreSQL 9.2 中提取字符串

  • 772

我得到以下列：

full_path "gorfs"."absolute_pathname"

它保留 BLOB 的路径，如下例所示：

在此处输入图像描述

请注意，这4865是account_id.The 路径总是像：/userfiles/account/ACCOUNTID

我需要提取它，以便在另一个查询中使用。例如：

select * from gorfs.seg WHERE account_id = 4865

如何从full_path列中提取该值？

postgresql string

3 个回答

  1. Best Answer

    如果账户id总是在第三位使用split_part()

    select * 
from gorfs.seg 
WHERE split_part(full_path, '/', 4) = '4865'

    请注意，由于前导/，实际索引是 4，而不是 3。

    以下查询：

    with seg (id, full_path) as (
  values 
      (1, '/userfiles/account/4556/attachment/1234'), 
      (2, '/userfiles/account/123/attachment/1234'), 
      (3, '/userfiles/account/7890/attachment/1234'),
      (4, '/userfiles/account/4556/attachment/56789')
)
select *
from seg
where split_part(full_path, '/', 4) = '4556'

    回报

    id | full_path                               
---+-----------------------------------------
 1 | /userfiles/account/4556/attachment/1234 
 4 | /userfiles/account/4556/attachment/56789

    如果你想把它放到一个视图中，你可以使用：

    create or replace view v_seg
as
select st_ino, 
       segment_index, 
       st_ino_target, 
       full_path, 
       split_part(full_path, '/', 4) as account_id
from gorfs.seg;
    • 4

  2. 事实证明这是一个棘手的谜题，比我想象的要花费更多的时间——我想你也是吗？:-)。

    我（最终）设法解决了这个问题：首先，我创建了一个名为 stringtest 的表（使用 DDL 和 DML 供那些希望在家尝试的人使用）：

    CREATE TABLE stringtest (my_string VARCHAR(255));

    并将其填充如下

    INSERT INTO stringtest VALUES('/blah/moreblah/4556/stuff/file.name');
INSERT INTO stringtest VALUES('/blah/moreblah/stuff/3434/file.name');
INSERT INTO stringtest VALUES('/users/test/34343/sfdsfs/6778');
INSERT INTO stringtest VALUES('/users/test/34343/sfdsfs/677990');

    然后我使用了 regexp_matches，因为我认为这很自然

    ntest=# SELECT (regexp_matches(my_string, '\d+')) FROM stringtest;
 regexp_matches 
----------------
 {4556}
 {3434}
 {34343}
 {34343}
(4 rows)

    所以，我想，这很简单 - 两秒钟内完成。我错了！

    然后我尝试了各种类型转换（链接的和单个的！）、函数和各种谷歌搜索都无济于事。

    这个问题的关键是要意识到 regexp_matches 返回一个字符串数组（因此字段周围有小花括号）。因此，PostgreSQL（也许不足为奇）无法将字符串数组转换为整数。随之而来的是更疯狂的谷歌搜索，我发现了这颗宝石。

    关键是 array_to_string 函数。我想出的答案是这样的：

    ntest=# SELECT ((array_to_string(regexp_matches(my_string, '\d+', 'i'), ';'))::integer) + 5 
AS IntTest FROM stringtest;

 inttest 
---------
    4561
    3439
   34348
   34348
(4 rows)

    这+5不是绝对必要的（注意值的变化）——它只是一个测试来证明它们确实被视为INTEGERs。显然，因为它们是account_ids，所以您不会对它们执行算术运算。

    [编辑]

    在 OP 关于添加WHERE子句的评论之后，我尝试了以下操作。一个简单的WHERE(...array_to_string = value...) 似乎不起作用。

    接下来，从这里开始，我尝试了

    ntest=# CREATE FUNCTION colint(stringtest)
ntest-#   RETURNS integer AS
ntest-# $func$
ntest$#     SELECT (array_to_string(regexp_matches(my_string, '\d+', 'i'), ';'))::integer
ntest$#     FROM   stringtest
ntest$# $func$ LANGUAGE SQL STABLE;
CREATE FUNCTION

    但是，这不起作用 - 似乎是 PostgreSQL AFICS 中的错误。

    ntest=# select *, stringtest.colint from stringtest; 
              my_string              | colint 
-------------------------------------+--------
 /blah/moreblah/4556/stuff/file.name |   4556
 /blah/moreblah/stuff/3434/file.name |   4556
 /users/test/34343/sfdsfs/6778       |   4556
 /users/test/34343/sfdsfs/677990     |   4556
(4 rows)

    绝对不好 - 我函数的所有派生值都具有相同的值。

    所以，接下来我在这里尝试使用 aCTE和 bingo！

    WITH tab AS
(
  SELECT *, (array_to_string(regexp_matches(my_string, '\d+', 'i'), ';'))::integer AS t
  FROM stringtest
)
SELECT * FROM tab
WHERE tab.t = 3434;

    结果：

        my_string              |  t   
-------------------------------------+------
 /blah/moreblah/stuff/3434/file.name | 3434
(1 row)

    看起来有点 hack，但它完成了工作！:-) 如果您只想要 stringtest 中的特定列，请在选项卡中单独命名列 - 例如

    ntest=# WITH tab AS
ntest-# (
ntest(#   SELECT my_string, (array_to_string(regexp_matches(my_string, '\d+', 'i'), ';'))::integer AS t
ntest(#   FROM stringtest
ntest(# )
ntest-# SELECT my_string FROM tab
ntest-# WHERE tab.t = 3434;
              my_string              
-------------------------------------
 /blah/moreblah/stuff/3434/file.name
(1 row)

    最后的想法是，TRIGGER在表上INSERT放置一个并在一个仅包含整数的附加字段中执行一个操作可能同样好account_id——这将使搜索变得非常高效——遗憾的是 PostgreSQL 的 [persisted | 计算 | 计算 | generated] 字段功能无法胜任 - 如果使用得当，它们会非常酷。

    [最终编辑]

    我在想这不应该那么棘手 - 然后我思考了Occam 的剃刀- 不要让事情变得比他们必须的更复杂 - 或者KISS。你会相信这是一种享受吗？

    CREATE VIEW vtest AS 
SELECT *, (SELECT ((array_to_string(regexp_matches(my_string, '\d+', 'i'), ';'))::integer) 
AS account_id)
FROM stringtest;

    现在的观点：

    ntest=# select * from vtest;
              my_string              | account_id 
-------------------------------------+------------
 /blah/moreblah/4556/stuff/file.name |       4556
 /blah/moreblah/stuff/3434/file.name |       3434
 /users/test/34343/sfdsfs/6778       |      34343
 /users/test/34343/sfdsfs/677990     |      34343
(4 rows)

    并且，从 same withWHERE子句中选择：

    ntest=# select * from vtest where account_id = 4556;

    给出（吞咽……毕竟！:-)）

                  my_string              | account_id 
-------------------------------------+------------
 /blah/moreblah/4556/stuff/file.name |       4556
(1 row)
    • 3

  3. ...或者您可以简单地使用括号来隔离组件并像往常一样进行转换：

    select ((regexp_matches(full_path, '\d+'))[1])::int
from gorfs.seg
where account_id = 4865

    问题是：如果子句中已经有了，为什么还需要从中提取 the account_id？full_pathwhere

    • 1

相关问题