How determine if range list contains specified integer

user_742402 0
Viewed 67

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.

plpgsql join postgresql arrays sql
2 Answers

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.

user_23020320

dbfiddle.uk demo will only works on pg14, since only pg14 have multirange data type. But customizeable icu collation works in pg13.
Collation doc: https://www.postgresql.org/docs/current/collation.html

Idea: create a multirange text data type that will sort numeric value based on their numerical value. like 'A-21' < 'A-123'.

CREATE COLLATION testcoll_numeric (
    provider = icu,
    locale = '@colNumeric=yes'
);

CREATE TYPE textrange AS RANGE (
    subtype = text,
    multirange_type_name = mulitrange_of_text,
    COLLATION = testcoll_numeric
);

So

SELECT
    mulitrange_of_text (textrange ('1'::text, '11'::text)) @> '9'::text AS contain_9;

should return true.

artliik table structure remain the same, but strings table need to change a bit.

CREATE temp TABLE strings (
    id text PRIMARY KEY,
    kirjeldLku mulitrange_of_text
);

then query it:

SELECT DISTINCT
    strings.id
FROM
    artliik,
    strings
WHERE
    strings.kirjeldLku @> liiginrlki::text
ORDER BY
    1;
user_156034770
Related