Re: Postgres array parser

Hello

do you know FOREACH IN ARRAY statement in 9.1

this significantly accelerate iteration over array

http://www.depesz.com/index.php/2011/03/07/waiting-for-9-1-foreach-in-array/

2011/12/13 Aleksej Trofimov <aleksej(dot)trofimov(at)ruptela(dot)lt>:
> 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?
>

you can use indexes or you can use hstore

Regards

Pavel Stehule

> --
> Best regards
>
> Aleksej Trofimov
>
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance