Extract string in PostgreSQL 9.2

Question

I got the following column:

full_path "gorfs"."absolute_pathname"

It keeps the path of a BLOB, like on the example below:

Note that the 4865 is the account_id.The path is always like: /userfiles/account/ACCOUNTID

I need to extract that, to use in another query. For example:

select * from gorfs.seg WHERE account_id = 4865

How can I extract that value from the full_path column?

Answer 1

If the account id is always at the third position use split_part()

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

Note that due to the leading / the actual index is 4, not 3.

The following query:

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'

returns

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

If you want to put that into a view, you can use:

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;

Answer 2

This turned out to be a tricky puzzle which took up far more time than I thought it would - I guess for you too? :-).

I (finally) managed to solve it thus: First I created (using DDL and DML for those who wish to try this at home) a table called stringtest:

CREATE TABLE stringtest (my_string VARCHAR(255));

and populated it as below

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');

Then I used regexp_matches as I thought would be natural

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

So, I thought, this is easy-peasy - over in two seconds. How wrong was I!!

I then tried all sorts of typecasts (chained as well as single!), functions and assorted Googling to no avail.

The key to this problem is to realise that regexp_matches returns an array (hence the little curly braces around the fields) of strings. So, PostgreSQL (unsurpsingly perhaps) can't convert an array of strings into an integer. More frantic Googling ensued and I came across this gem.

The key is the array_to_string function. The answer I came up with is this:

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

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

The +5 isn't strictly necessary (note the change in values) - it was just a test to prove that they are indeed treated as INTEGERs. Obviously, since they're account_ids, you won't be performing arithmetic on them.

[EDIT]

Following the OP's comment below about adding a WHERE clause, I tried the following. A simple WHERE (...array_to_string = value...) doesn't appear to work.

Next, from here, I tried

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

but, that doesn't work - appears to be a bug in 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)

Definitely bad - same value for all of my function's derived values.

So, next I tried here using a CTE and 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;

Result:

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

Seems like a bit of a hack, but it gets the job done! :-) If you only want specific columns in stringtest, name the columns individually in tab - e.g.

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)

Final thought, it might be just as well to have a TRIGGER on the table and do an INSERT into an additional field of just the account_id integer - that would make it very efficient for searching - it's a pity that PostgreSQL's [persisted | calculated | computed | generated] field functionality isn't up to the job - they are pretty cool when used correctly.

[FINAL EDIT]

I was thinking that this shouldn't be that tricky - and then I pondered Occam's razor - don't make things more complicated than they have to be - or K.I.S.S.. Would you believe that this works a treat?

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

Now the view:

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)

And, selecting from same with WHERE clause:

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

gives (gulp... after all that! :-) )

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

Answer 3

...or you can simply use parenthesis to isolate the components and cast as usual:

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

The question is: why do you need to extract the account_id from the full_path if you already have it in the where clause?