Hello, I wanted to ask according such a problem which we had faced with.
We are widely using postgres arrays like key->value array by doing like
this:
{{1,5},{2,6},{3,7}}
where 1,2,3 are keys, and 5,6,7 are values. In our pgSql functions we
are using self written array_input(array::numeric[], key::numeric)
function which makes a loop on whole array and searches for key like
FOR i IN 1 .. size LOOP
if array[i][1] = key then
return array[i][2];
end if;
END LOOP;
But this was a good solution until our arrays and database had grown. So
now FOR loop takes a lot of time to find value of an array.
And my question is, how this problem of performance could be solved? We
had tried pgperl for string parsing, but it takes much more time than
our current solution. Also we are thinking about self-written C++
function, may be someone had implemented this algorithm before?
--
Best regards
Aleksej Trofimov