How determine if range list contains specified integer-CodePudding

Product type table contains product types. Some ids may missing :

create table artliik (liiginrlki char(3) primary key);
insert into artliik values('1');
insert into artliik values('3');
insert into artliik values('4');
...
insert into artliik values('999');

Property table contais comma separated list of types.

create table strings ( id char(100) primary key, kirjeldLku chr(200) );
insert into strings values ('item1', '1,4-5' );
insert into strings values ('item2', '1,2,3,6-9,23-44,45' );

Type can specified as single integer, e.q 1,2,3 or as range like 6-9 or 23-44 List can contain both of them.

How to all properties for given type. Query

select id 
from artliik
join strings on ','||trim(strings.kirjeldLku)||',' like '%,'||trim(artliik.liiginrlki)||',%'

returns date for single integer list only. How to change join so that type ranges in list like 6-9 are also returned? Eq. f list contains 6-9, Type 6,7,8 and 9 shoud included in report.

Postgres 13 is used.

CodePudding user response：

I would suggest a helper function similar to unnest that honors ranges.

Corrected function

create or replace function unnest_ranges(s text)
returns setof text language sql immutable as
$$
 with t(x) as (select unnest(string_to_array(s, ',')))
 select generate_series
 (
   split_part(x, '-', 1)::int, 
   case when x ~ '-' then split_part(x, '-', 2)::int else x::int end,
   1
 )::text
 from t;
$$;

Then you can 'normalize' table strings and join.

select * 
from artliik a 
join (select id, unnest_ranges(kirjeldLku) from strings) as t(id, v)
on a.liiginrlki = v;

The use of a function definition is of course optional. I prefer it because the function is generic and reusable.