Re: plpgsql arrays

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On Fri, Apr 3, 2009 at 9:32 AM, Matthew Wakeling <matthew@xxxxxxxxxxx> wrote:
>  genes = '{}';
>  next_new = 1;
>  FOR loc IN SELECT location.* FROM location, gene WHERE location.subjectid =
> gene.id ORDER BY objectid, intermine_start, intermine_end LOOP
>     genes[next_new] = loc;
>     IF (next_new % 10000 = 0) THEN
>         RAISE NOTICE 'Scanned % gene locations', next_new;
>     END IF;
>     next_new = next_new + 1;
>  END LOOP;
>  genes_size = coalesce(array_upper(genes, 1), 0);
>  RAISE NOTICE 'Scanned % gene locations', genes_size;
>
> For 200,000 rows it takes 40 minutes.
>
> So, is there a way to dump the results of a query into an array quickly in
> plpgsql, or alternatively is there a way to read two results streams
> simultaneously?

try this:
select array(SELECT location.* FROM location, gene WHERE
location.subjectid = gene.id ORDER BY objectid, intermine_start,
intermine_end)) into genes;

merlin

-- 
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux