Обсуждение: Qusetion re regexexp_split_to_array and last occurence
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)],'(.*)\.(.*)');
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
On Tue, Oct 8, 2019 at 10:31 AM Mike Martin <mike@redtux.plus.com> wrote:
arrfile=(regexp_split_to_array(NEW.tagfile,'/'))[2:];
string_to_array()? You aren't providing a regexp so it seems wasteful to use the regexp engine to perform the task.
NEW.filearr :=arrfile[1:cardinality(arrfile)-1]||regexp_matches(arrfile[cardinality(arrfile)],'(.*)\.(.*)');
regexp_match()? You aren't returning a set of matches so use the scalar function. Probably still need to deal with null properly.
Which works perfectly, except performance is 50% slower on a dataset of around 20kIs there a better solution
Probably not materially - it seems like you have to do it in two parts - one to break apart the paths and then one to handle the fact that you want the part subsequent to the final period (i.e., the file extension) in its own array cell. Choosing the best function for each job will hopefully improve matters at least a bit.
David J.