Qusetion re regexexp_split_to_array and last occurence

From: Mike Martin <mike(at)redtux(dot)plus(dot)com>
To: pgsql-sql(at)lists(dot)postgresql(dot)org
Subject: Qusetion re regexexp_split_to_array and last occurence
Date: 2019-10-08 15:48:40
Message-ID: CAOwYNKb7mRHZvxoahPKRd2m2ZpSEr3B=+-XgoYZZK5ZZJgypfg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

Hi
I have the following in a trigger (update/insert)
NEW.filearr := (regexp_split_to_array(NEW.tagfile,'(?:/|\.)'))[2:];

This works except when there is a period in the filename

So I did this instead

arrfile=(regexp_split_to_array(NEW.tagfile,'/'))[2:];
NEW.filearr
:=arrfile[1:cardinality(arrfile)-1]||regexp_matches(arrfile[cardinality(arrfile)],'(.*)\.(.*)');

Which works perfectly, except performance is 50% slower on a dataset of
around 20k

Is there a better solution

Mike

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Mike Martin 2019-10-08 15:51:01 Question re regexexp_split_to_array and last oc
Previous Message Tom Lane 2019-10-08 14:29:30 Re: parser that could handle "FROM... SELECT..." as well as "SELECT... FROM..."