Re: Overlap function for hstore?

Hi David

2015-04-04 3:16 GMT+02:00 David G. Johnston <david.g.johnston@gmail.com>:
> Not tested!
>
> SELECT hstore(array_agg(key), array_agg(value)) FROM (
> SELECT each(...)
> INTERSECT
> SELECT each(...)
> ) src
>
> David J.

Thanks!

SELECT hstore(array_agg((tmp_attr).key), array_agg((tmp_attr).value))
FROM (
SELECT each((select 'a=>1,b=>2,c=>3'::hstore)) as tmp_attr
INTERSECT ALL
SELECT each((select 'a=>2,d=>4,b=>2'::hstore)) as tmp_attr
) tmp_table;

Cheers, S,



2015-04-04 3:16 GMT+02:00 David G. Johnston <david.g.johnston@gmail.com>:
> On Fri, Apr 3, 2015 at 5:37 PM, Stefan Keller <sfkeller@gmail.com> wrote:
>>
>> Hi,
>>
>> I'd like to get an overlap function similar to '&&' but for key-value
>> pairs of hstore!
>>
>> This underfits:
>>
>> postgres=# select hstore_to_array('a=>1,b=>2,c=>3'::hstore) &&
>> hstore_to_array('a=>2,d=>4,b=>2'::hstore)
>>
>> ...because array overlaps op takes every element (even 'a' or 2 alone)
>> and doesn't test for key and value together like in 'b=>2'!
>>
>> Any clever ideas?
>
>
> Not tested!
>
> SELECT hstore(array_agg(key), array_agg(value)) FROM (
> SELECT each(...)
> INTERSECT
> SELECT each(...)
> ) src
>
> David J.
>