Postgresql array parser

From: Aleksej Trofimov <aleksej(dot)trofimov(at)ruptela(dot)lt>
To: pgsql-novice(at)postgresql(dot)org
Subject: Postgresql array parser
Date: 2011-12-13 09:16:50
Message-ID: 4EE71802.5020707@ruptela.lt
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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?

--

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Thomas Kellerer 2011-12-13 09:33:58 Re: SELECT from two tables with different field names?
Previous Message M. Emre Çolak 2011-12-13 08:52:29 getting auto increment id value