Question 292516 in PostgreSQL Question posted in
The official documentation can be found here.

Emulating FS create file behavior in PostgreSQL

I got a files table:

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

And I wanna write an sql transaction to emulate FS behavior when you tryna create a file:

  • if a file with such path doesn’t exist yet -> create it
  • if it does -> create a file with name like {filename} (1)
  • if such file already exists too -> find the greatest available number n and paste it into {filename} ({n})

So I’m stuck in that moment:

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

that gives me a sorted array (or a query) of such numbers. But how to find the next greatest available number?

Maybe you got something better in your mind cause this query already looks too enormous for such task IMO

DB Fiddle to play with it

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    After realizing that commenters and that indian retard can't read so they wrote some sht, I spent some hours and finally solved it.

    My commented attempts with using the LAG window function were in a right way. So the final answer is:

    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;

    I separated kek as it's gonna be an argument, not just a part of a plain query how dumbs above were assuming. And the magic number 6 is also a pasted value since it's a length of the kek ( part and I'm gonna calculate it in my program and paste it to the plain query

    And it's also available as a DB Fiddle


  2. 0

    Do you provide a filename to generate a new file number for the specific file?

    I have tried to solve it but if you could let us what input will be provided to the query and what output you expect?

    This query will generate the latest file number + 1 for the patterns provided in the where clause in the CTE;

    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

    Output

     - filename      Filenumber
----------------------------
cheburek                  1
kek                      11
kek and bek             334
lol                       1
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search