hstore_match_key_prefix.sql

--
-- FUNCTION hstore_match_key_prefix(...)
-- 
--drop function hstore_match_key_prefix(hstore, text, text);
create or replace function hstore_match_key_prefix(tags hstore, key_prefix text, value_value text default 'yes')
returns text[] 
as $$ 
select array_agg(substring(key, key_prefix||':#"%#"', '#')) 
from (select (each(tags)).key as key, (each(tags)).value as value) as "alias"
where key like key_prefix||':%' and value = value_value
order by 1
$$ language sql;

with tbl(tags) as (values
    ('a:a=>yes,payment:cash=>no,payment:american_express=>yes, payment:diners_club=>yes'::hstore),
    ('a:b=>yes,payment:cash=>yes,payment:american_express=>no'::hstore),
    ('a:c=>yes,payment:cash=>no'),
    ('a:d=>yes')
)
select tags, hstore_match_key_prefix(tags, 'payment') as payment
from tbl
--where hstore_match_key_prefix(tags, 'payment', 'no') is null -- payment:*=no oder kein payment 
where hstore_match_key_prefix(tags, 'payment') && '{american_express, diners_club}';
*/


--
-- Demo: List all supermarkets which accept Twint in OpenStreetMap (EOSMDBTwo)
--
select osm_id, name, tags, geom
from osm_point
where tags->'shop' in ('supermarket','convenience','deli') 
and (hstore_match_key_prefix(tags, 'payment') && '{twint}');