在PostgreSQL中模拟FS创建文件行为

6rvt4ljy 于 11个月前发布在 PostgreSQL

关注(0)|答案(2)|浏览(157)

我有一个文件表：

CREATE TABLE files (
    id   SERIAL  PRIMARY KEY,
    path VARCHAR NOT NULL UNIQUE
);

字符串
我想写一个sql transaction来模拟当你试图创建一个文件时的FS行为：

如果具有该路径文件尚不存在->创建它
如果是->创建一个名为{filename} (1)的文件
如果这样的文件已经存在->找到最大的可用数字n并将其粘贴到{filename} ({n})中

所以我被困在那一刻：

WITH f AS (
  SELECT path
  FROM files
  WHERE path ~ '^(kek|kek \(\d+\))$'
  ORDER BY path DESC
)
SELECT 
    CASE
        WHEN COUNT(f) > 1 THEN (
          WITH cte AS (
            SELECT *
            FROM (
              SELECT SUBSTRING(f.path, 6, LENGTH(f.path) - 6)::numeric AS i
              FROM f
              WHERE f.path != 'kek'
            ) AS n
            WHERE i > 0
            ORDER BY i
          )
          -- SELECT LAG(arr, 1, NULL) OVER (
          --     PARTITION BY arr
          --     ORDER BY arr
          --     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
          --   )
          -- FROM a
          SELECT ARRAY(
            SELECT i + 1
            FROM cte
          )
        )
        WHEN COUNT(f) = 0 THEN 'kek (0)'
        ELSE 'kek'
    END
FROM f

型
这给了我一个排序的数组（或查询）这样的数字。但如何找到下一个最大的可用数字？
也许你有更好的东西在你的脑海中，因为这个查询已经看起来太庞大，这样的任务海事组织

DB Fiddle to play with it

postgresql

来源：https://stackoverflow.com/questions/77546053/emulating-fs-create-file-behavior-in-postgresql

2条答案

按热度按时间

lf3rwulv1#

您是否提供文件名以生成特定文件的新文件编号？
我试图解决它，但如果你能让我们什么输入将提供给查询和什么输出你的期望？
这个查询将为CTE中的where子句中提供的模式生成最新的文件号+1;

DROP TABLE IF EXISTS files;

CREATE TABLE files (
    id   SERIAL  PRIMARY KEY,
    path VARCHAR NOT NULL UNIQUE
);

INSERT INTO files (path) VALUES
    ('lol'),
    ('kek and bek (333)'),
    ('kek (1)'),
    ('kek (2)'),
    ('kek (9)'),
    ('kek (10)'),
    ('kek (1)(3)'),
    ('kek (1)(3)(400)'),
    ('kek (1)(2)(401)'),
    ('cheburek');

WITH CTE AS (
SELECT
    path, 
    CASE 
        WHEN POSITION('(' IN path) > 0 THEN SUBSTRING(path FROM 1 FOR POSITION('(' IN path) - 1)
            ELSE path
        END AS fileName -- before_opening_parenthesis
        , STRING_TO_ARRAY(replace(replace(replace(path, ')(', '-'), '(', ''), ')',''), ' ')  AS words_array
        , STRING_TO_ARRAY(replace(replace(replace(path, ')(', ' '), '(', ''), ')',''), ' ')  AS words_array2
        , NULLIF(regexp_replace(path::text, '\D','','g'), '')::numeric as num
FROM 
  files
  WHERE 
    path ~ '^(lol)$' or 
    path ~ '^(kek and bek \(\d+\))$' or 
    path ~ '^(cheburek)$' or 
    path ~ '^(kek|kek \(\d+\))$'
) 
SELECT filename, max(Coalesce(num,0))+1 Filenumber FROM CTE
GROUP by filename

字符串
输出

- filename      Filenumber
----------------------------
cheburek                  1
kek                      11
kek and bek             334
lol                       1

型

赞(0）回复(0）举报 11个月前

ca1c2owp2#

我使用LAG窗口函数的注解尝试是正确的。所以最终的答案是：

INSERT INTO files (path)
WITH f AS (
  SELECT path
  FROM files
  WHERE path ~ ('^(' || 'kek' || '|' || 'kek' || ' \(\d+\))$')
  ORDER BY path DESC
)
SELECT 
    CASE
        WHEN NOT EXISTS(SELECT f.path FROM f WHERE f.path = 'kek') THEN 'kek'
        ELSE
            CASE
                WHEN COUNT(f) > 1 THEN (
                    WITH cte AS (
                      SELECT *
                      FROM (
                        SELECT SUBSTRING(f.path, 6, LENGTH(f.path) - 6)::numeric AS i
                        FROM f
                        WHERE f.path != 'kek'
                      ) AS n
                      WHERE i > 0
                      ORDER BY i
                    )
                    SELECT 'kek' || '(' || COALESCE(t.next_i, (SELECT cte.i + 1 FROM cte ORDER BY cte.i DESC LIMIT 1)) || ')'
                    FROM cte
                    FULL OUTER JOIN (
                      SELECT prev_i + 1 AS next_i
                      FROM (
                        SELECT LAG(i, 1, 0) OVER() AS prev_i, i
                        FROM cte
                      ) t
                      WHERE prev_i != t.i - 1
                      LIMIT 1
                    ) t ON cte.i = t.next_i
                    LIMIT 1
                )
                WHEN COUNT(f) = 0 THEN 'kek' || ' (1)'
                ELSE 'kek'
        END
    END
FROM f;

字符串
我将kek分隔开来，因为它将成为一个参数，而不仅仅是普通查询的一部分。幻数6也是一个粘贴值，因为它是kek (部分的长度，我将在程序中计算它并将其粘贴到普通查询。
它也可以作为DB Fiddle提供

赞(0）回复(0）举报 11个月前

我来回答

在PostgreSQL中模拟FS创建文件行为

DB Fiddle to play with it

2条答案

相关问题

热门标签

最新问答