Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Help with an interesting Postgres question: Why isn't an Index Only Scan used on a partition accessed via the parent table?.

select version();


version


PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.4.1 20200928 (Red Hat 8.4.1-1), 64-bit


create table mtable( mid integer generated always as identity 
                   , col1 text
                   ); 
                  
insert into mtable (col1) 
     values ('REQUIRED STRING') 
          , ('REQUIRED STRING, Optional A, Optional B')
          , ('Optional A, REQUIRED STRING, Optional B') 
          , ('Optional A, Oprional X, Optional B')   
          , ('REQUIRED STRING 842, Lessor Optional A, Optional B');             
         
select * from mtable;
5 rows affected



mid
col1


1
REQUIRED STRING

2
REQUIRED STRING, Optional A, Optional B

3
Optional A, REQUIRED STRING, Optional B

4
Optional A, Oprional X, Optional B

5
REQUIRED STRING 842, Lessor Optional A, Optional B


create or replace 
function like_with_and_or(
         required_string text 
       , optional_strigs text[] 
       )
  returns setof mtable
  language sql 
as $$
   select * 
     from mtable mt  
     where mt.col1 like '%' || required_string ||  '%'
       and (   array_ndims(optional_strigs) is null     -- no optional_strigs
            or exists ( select null
                          from unnest(optional_strigs) os
                         where mt.col1 like '%' || os ||  '%' 
                      ) 
           );
$$; 
select * 
  from like_with_and_or('REQUIRED STRING'::text
     , array['Optional D','Optional C','Optional B'] );


mid
col1


2
REQUIRED STRING, Optional A, Optional B

3
Optional A, REQUIRED STRING, Optional B

5
REQUIRED STRING 842, Lessor Optional A, Optional B


select * from like_with_and_or('REQUIRED STRING'::text, array[]::text[]);


mid
col1


1
REQUIRED STRING

2
REQUIRED STRING, Optional A, Optional B

3
Optional A, REQUIRED STRING, Optional B

5
REQUIRED STRING 842, Lessor Optional A, Optional B


select * from like_with_and_or('REQUIRED STRING'::text, array['Optional A']::text[])


mid
col1


2
REQUIRED STRING, Optional A, Optional B

3
Optional A, REQUIRED STRING, Optional B

5
REQUIRED STRING 842, Lessor Optional A, Optional B


select * from like_with_and_or('REQUIRED STRING'::text, array['Optional Y','Optional Z']::text[])

mid	col1
1	REQUIRED STRING
2	REQUIRED STRING, Optional A, Optional B
3	Optional A, REQUIRED STRING, Optional B
4	Optional A, Oprional X, Optional B
5	REQUIRED STRING 842, Lessor Optional A, Optional B