Finding first array value that contains a string in PostgreSql

Question

SELECT array_position(ARRAY, 'fillfactor=%') as position
FROM table;

I want to be able to use code similar to above. I do not know what the fill factor is before hand so i cannot do an exact string match, instead i want to grab the first value that starts with that statement. Note this sample assumes ARRAY is a text[] column. Can anyone please help?

Answer 1

There's no built-in function like array_regex_position or similar that would be able to do this. You will need to unnest your array and use a pattern-matching operator or function on the resulting elements. On the assumption you're querying pg_class.reloptions, this should give you the general idea (this is on 10.5, I don't have any 9.X servers running right now):

testdb=# create table t_with_fillfactor(asdf text) with (fillfactor=50);
CREATE TABLE
testdb=# select relname,
    substr(reloption, length('fillfactor=')+1)::integer as fill_factor
from pg_class, unnest(reloptions) as reloption
where relname='t_with_fillfactor'
  and reloption ~ 'fillfactor=.*';
      relname      | fill_factor 
-------------------+-------------
 t_with_fillfactor |          50
(1 row)