Thread: plpgsql arrays

plpgsql arrays

From
Matthew Wakeling
Date:
I'm writing a plpgsql function that effectively does a merge join on the
results of two queries. Now, it appears that I cannot read the results of
two queries as streams in plpgsql, so I need to copy the contents of one
query into an array first, and then iterate over the second query
afterwards.

I have discovered that creating large arrays in plpgql is rather slow. In
fact, it seems to be O(n^2). The following code fragment is incredibly
slow:

  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?

Matthew

--
 I would like to think that in this day and age people would know better than
 to open executables in an e-mail. I'd also like to be able to flap my arms
 and fly to the moon.                                    -- Tim Mullen

Re: plpgsql arrays

From
Robert Haas
Date:
On Fri, Apr 3, 2009 at 9:32 AM, Matthew Wakeling <matthew@flymine.org> wrote:
> I'm writing a plpgsql function that effectively does a merge join on the
> results of two queries. Now, it appears that I cannot read the results of
> two queries as streams in plpgsql, so I need to copy the contents of one
> query into an array first, and then iterate over the second query
> afterwards.

Why not just use SQL to do the join?

...Robert

Re: plpgsql arrays

From
Matthew Wakeling
Date:
On Fri, 3 Apr 2009, Robert Haas wrote:
> On Fri, Apr 3, 2009 at 9:32 AM, Matthew Wakeling <matthew@flymine.org> wrote:
>> I'm writing a plpgsql function that effectively does a merge join on the
>> results of two queries.

> Why not just use SQL to do the join?

Because the merge condition is:

WHERE l1.start <= l2.end AND l2.start <= l1.end

and merge joins in postgres only currently cope with the case where the
merge condition is an equals relationship.

Oh, hang on, I think I saw something in the docs about what conditions can
be used in a merge...

Matthew

--
 Let's say I go into a field and I hear "baa baa baa". Now, how do I work
 out whether that was "baa" followed by "baa baa", or if it was "baa baa"
 followed by "baa"?
         - Computer Science Lecturer

Re: plpgsql arrays

From
Tom Lane
Date:
Matthew Wakeling <matthew@flymine.org> writes:
> On Fri, 3 Apr 2009, Robert Haas wrote:
>> Why not just use SQL to do the join?

> Because the merge condition is:

> WHERE l1.start <= l2.end AND l2.start <= l1.end

> and merge joins in postgres only currently cope with the case where the
> merge condition is an equals relationship.

> Oh, hang on, I think I saw something in the docs about what conditions can
> be used in a merge...

No, you got it right the first time.  I was about to suggest that maybe
you could make it work by recasting the problem as equality on an
interval datatype, but the problem is that this is not equality but
"overlaps".  And you can't cheat and call it equality, because it's
not transitive.

I don't actually believe that a standard merge join algorithm will work
with an intransitive join condition ...

            regards, tom lane

Re: plpgsql arrays

From
Tom Lane
Date:
Matthew Wakeling <matthew@flymine.org> writes:
> I have discovered that creating large arrays in plpgql is rather slow. In
> fact, it seems to be O(n^2).

For variable-width element types, yeah.  Don't go that way.

> ... alternatively is there a way to read two results streams
> simultaneously?

Use two cursors and FETCH from each as needed?  In recent releases you
can even scroll backwards, which you're going to need to do to make
a merge join work.

            regards, tom lane

Re: plpgsql arrays

From
Matthew Wakeling
Date:
On Fri, 3 Apr 2009, Tom Lane wrote:
>> Oh, hang on, I think I saw something in the docs about what conditions can
>> be used in a merge...
>
> No, you got it right the first time.  I was about to suggest that maybe
> you could make it work by recasting the problem as equality on an
> interval datatype, but the problem is that this is not equality but
> "overlaps".  And you can't cheat and call it equality, because it's
> not transitive.

Well, according to
http://www.postgresql.org/docs/8.3/interactive/xoper-optimization.html#AEN41844

| So, both data types must be capable of being fully ordered, and the
| join operator must be one that can only succeed for pairs of values that
| fall at the "same place" in the sort order.

> I don't actually believe that a standard merge join algorithm will work
> with an intransitive join condition ...

A standard merge join should work absolutely fine, depending on how it's
implemented. If the implementation keeps a list of "current" right-hand
elements, and adds right-hand rows to the list when they compare "equal"
to the current left-hand element, and removes them from the list when they
compare "not equal" to the current left-hand element, then it would work
fine. If it does something else like rewinding the right-hand stream, or
throwing away the list when the current left-hand element is "not equal"
the previous left-hand element, (which would be fine for true equality)
then it will not work.

The description in the docs doesn't make it clear which way Postgres does
it.

Matthew

--
 I have an inferiority complex. But it's not a very good one.

Re: plpgsql arrays

From
Tom Lane
Date:
Matthew Wakeling <matthew@flymine.org> writes:
> On Fri, 3 Apr 2009, Tom Lane wrote:
>> I don't actually believe that a standard merge join algorithm will work
>> with an intransitive join condition ...

> A standard merge join should work absolutely fine, depending on how it's
> implemented. If the implementation keeps a list of "current" right-hand
> elements, and adds right-hand rows to the list when they compare "equal"
> to the current left-hand element, and removes them from the list when they
> compare "not equal" to the current left-hand element, then it would work
> fine.

No, it would not.  Not unless you have sorted the inputs in some way
that has more knowledge than the "equal" operator represents.  Otherwise
you can have elements drop out that might still be needed to match to a
later left-hand element.  Remember the point of the intransitivity
assumption: there can be a right-hand element X that is "equal" to two
left-hand elements Y and Z, but Y and Z are not "equal" to each other
and thus might not be kept adjacent in the left-hand sorting.

            regards, tom lane

Re: plpgsql arrays

From
Matthew Wakeling
Date:
On Fri, 3 Apr 2009, Tom Lane wrote:
> Not unless you have sorted the inputs in some way that has more
> knowledge than the "equal" operator represents.  Otherwise you can have
> elements drop out that might still be needed to match to a later
> left-hand element.

Of course. You certainly have to choose a sort order that works. Sorting
by the start field would be sufficient in this case.

Matthew

--
 For those of you who are into writing programs that are as obscure and
 complicated as possible, there are opportunities for... real fun here
                                        -- Computer Science Lecturer

Re: plpgsql arrays

From
Matthew Wakeling
Date:
On Fri, 3 Apr 2009, Matthew Wakeling wrote:
> On Fri, 3 Apr 2009, Tom Lane wrote:
>> Not unless you have sorted the inputs in some way that has more knowledge
>> than the "equal" operator represents.  Otherwise you can have elements drop
>> out that might still be needed to match to a later left-hand element.
>
> Of course. You certainly have to choose a sort order that works. Sorting by
> the start field would be sufficient in this case.

Oh &^%")(!. That algorithm only finds the matches where l1.start >=
l2.start. Yeah, you're quite right.

Matthew

--
 And why do I do it that way? Because I wish to remain sane. Um, actually,
 maybe I should just say I don't want to be any worse than I already am.
         - Computer Science Lecturer

Re: plpgsql arrays

From
Tom Lane
Date:
Matthew Wakeling <matthew@flymine.org> writes:
> On Fri, 3 Apr 2009, Tom Lane wrote:
>> Not unless you have sorted the inputs in some way that has more
>> knowledge than the "equal" operator represents.  Otherwise you can have
>> elements drop out that might still be needed to match to a later
>> left-hand element.

> Of course. You certainly have to choose a sort order that works. Sorting
> by the start field would be sufficient in this case.

Uh, no, it wouldn't.  Visually:

    L1    -------------------------
    L2    -----------
    L3    ---------------------

    R1                   --------

At L2, you'd conclude that you're done matching R1.

Intuitively, it seems like 1-D "overlaps" is a tractable enough
operator that you should be able to make something merge-like
work.  But it's more complicated than I think you realize.

            regards, tom lane

Re: plpgsql arrays

From
Matthew Wakeling
Date:
On Fri, 3 Apr 2009, Tom Lane wrote:
> Intuitively, it seems like 1-D "overlaps" is a tractable enough
> operator that you should be able to make something merge-like
> work.  But it's more complicated than I think you realize.

It's tractable when the two sides are symmetrical, but not so much when
they aren't. Our "no it isn't" messages obviously crossed on the wire.

Matthew

--
 Some people, when confronted with a problem, think "I know, I'll use regular
 expressions." Now they have two problems.                  -- Jamie Zawinski

Re: plpgsql arrays

From
Merlin Moncure
Date:
On Fri, Apr 3, 2009 at 9:32 AM, Matthew Wakeling <matthew@flymine.org> 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

Re: plpgsql arrays

From
Merlin Moncure
Date:
On Fri, Apr 3, 2009 at 11:02 AM, Merlin Moncure <mmoncure@gmail.com> wrote:
> On Fri, Apr 3, 2009 at 9:32 AM, Matthew Wakeling <matthew@flymine.org> 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;

one more time:
select array(SELECT location FROM location, gene WHERE
 location.subjectid = gene.id ORDER BY objectid, intermine_start,
 intermine_end)) into genes;

this will make array of location records.  when you access the records
to do the merge, make sure to use () noation:

if (genes[x]).field > something then
...

merlin

Re: plpgsql arrays

From
Matthew Wakeling
Date:
On Fri, 3 Apr 2009, Merlin Moncure wrote:
> select array(SELECT location FROM location, gene WHERE
> location.subjectid = gene.id ORDER BY objectid, intermine_start,
> intermine_end)) into genes;

Yeah, that works nicely.

> this will make array of location records.  when you access the records
> to do the merge, make sure to use () noation:
>
> if (genes[x]).field > something then

How is that different to genes[x].field?

Matthew

--
 And the lexer will say "Oh look, there's a null string. Oooh, there's
 another. And another.", and will fall over spectacularly when it realises
 there are actually rather a lot.
         - Computer Science Lecturer (edited)

Re: plpgsql arrays

From
Merlin Moncure
Date:
On Fri, Apr 3, 2009 at 11:15 AM, Matthew Wakeling <matthew@flymine.org> wrote:
> On Fri, 3 Apr 2009, Merlin Moncure wrote:
>>
>> select array(SELECT location FROM location, gene WHERE
>> location.subjectid = gene.id ORDER BY objectid, intermine_start,
>> intermine_end)) into genes;
>
> Yeah, that works nicely.
>
>> this will make array of location records.  when you access the records
>> to do the merge, make sure to use () noation:
>>
>> if (genes[x]).field > something then
>
> How is that different to genes[x].field?

ah, it isn't...in many cases where you access composite type fields,
() is required (especially in query).  it isn't here, so you can
safely leave it off.

merlin

Re: plpgsql arrays

From
Simon Riggs
Date:
On Fri, 2009-04-03 at 10:04 -0400, Tom Lane wrote:
> Matthew Wakeling <matthew@flymine.org> writes:
> > On Fri, 3 Apr 2009, Robert Haas wrote:
> >> Why not just use SQL to do the join?
>
> > Because the merge condition is:
>
> > WHERE l1.start <= l2.end AND l2.start <= l1.end
>
> > and merge joins in postgres only currently cope with the case where the
> > merge condition is an equals relationship.

(snip)

> I don't actually believe that a standard merge join algorithm will work
> with an intransitive join condition ...

I think it's a common enough problem that having a non-standard join
algorithm written for that case would be interesting indeed.

--
 Simon Riggs           www.2ndQuadrant.com
 PostgreSQL Training, Services and Support


Re: plpgsql arrays

From
Alvaro Herrera
Date:
Simon Riggs wrote:
>
> On Fri, 2009-04-03 at 10:04 -0400, Tom Lane wrote:

> > I don't actually believe that a standard merge join algorithm will work
> > with an intransitive join condition ...
>
> I think it's a common enough problem that having a non-standard join
> algorithm written for that case would be interesting indeed.

+42

--
Alvaro Herrera                                http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Re: plpgsql arrays

From
Tom Lane
Date:
Simon Riggs <simon@2ndQuadrant.com> writes:
> On Fri, 2009-04-03 at 10:04 -0400, Tom Lane wrote:
>> I don't actually believe that a standard merge join algorithm will work
>> with an intransitive join condition ...

> I think it's a common enough problem that having a non-standard join
> algorithm written for that case would be interesting indeed.

Sounds like a great PhD thesis topic.

            regards, tom lane

Re: plpgsql arrays

From
Nathan Boley
Date:
> Uh, no, it wouldn't.  Visually:
>
>        L1      -------------------------
>        L2      -----------
>        L3      ---------------------
>
>        R1                     --------
>
> At L2, you'd conclude that you're done matching R1.
>

No, you should conclude that you're done matching L2. You conclude
you're done matching R1 when you reach L4  ( or there exists a j st
Lj.start > R1.end, or equivalently Lj is strictly greater than R1 )

FWIW, this is a very common problem in bioinformatics. I've mostly
implemented this in python and C. The code is available at
encodestatistics.org. Look in encode.py at the overlap method of the
feature_region class, or ( for the C version ) region_overlap in
block_bootstrap.c ( svn is more up to date for the C ).

-Nathan

Re: plpgsql arrays

From
Matthew Wakeling
Date:
On Fri, 3 Apr 2009, Tom Lane wrote:
> Simon Riggs <simon@2ndQuadrant.com> writes:
>> On Fri, 2009-04-03 at 10:04 -0400, Tom Lane wrote:
>>> I don't actually believe that a standard merge join algorithm will work
>>> with an intransitive join condition ...
>
>> I think it's a common enough problem that having a non-standard join
>> algorithm written for that case would be interesting indeed.
>
> Sounds like a great PhD thesis topic.

I agree it'd be very cool to have a non-standard join algorithm for this
built into Postgres. However it is nowhere near complicated enough for a
PhD thesis topic.

I'm just putting the finishing touches on a plpgsql implementation - in
order to perform the join on a asymmetric set of ranges, you just need to
keep two separate history lists as you sweep through the two incoming
streams. This would be sufficient for range constraints.

Matthew

--
Surely the value of C++ is zero, but C's value is now 1?
  -- map36, commenting on the "No, C++ isn't equal to D. 'C' is undeclared
  [...] C++ should really be called 1" response to "C++ -- shouldn't it
  be called D?"

Re: plpgsql arrays

From
Matthew Wakeling
Date:
On Fri, 3 Apr 2009, Simon Riggs wrote:
> On Fri, 2009-04-03 at 10:04 -0400, Tom Lane wrote:
>> Matthew Wakeling <matthew@flymine.org> writes:
>>> On Fri, 3 Apr 2009, Robert Haas wrote:
>>>> Why not just use SQL to do the join?
>>>
>>> Because the merge condition is:
>>>
>>> WHERE l1.start <= l2.end AND l2.start <= l1.end
>>>
>>> and merge joins in postgres only currently cope with the case where the
>>> merge condition is an equals relationship.
>>
>> I don't actually believe that a standard merge join algorithm will work
>> with an intransitive join condition ...
>
> I think it's a common enough problem that having a non-standard join
> algorithm written for that case would be interesting indeed.

I'm currently trying to persuade my boss to give me time to do some work
to implement this in Postgres. It's not something I will be able to start
right away, but maybe in a little while.

I'm currently seeing this as being able to mark overlap constraints ("&&"
in quite a few data types) as "OVERLAP_MERGES", and have the planner be
able to use the new merge join algorithm. So it wouldn't help with the
exact query above, but would if I rewrote it to use the bioseg or spacial
data types' overlap operators.

I will need a little help as I am not incredibly familiar with the
Postgres innards. Would someone be able to do that?

Matthew

--
 Existence is a convenient concept to designate all of the files that an
 executable program can potentially process.   -- Fortran77 standard

Re: plpgsql arrays

From
Robert Haas
Date:
On Mon, Apr 6, 2009 at 8:52 AM, Matthew Wakeling <matthew@flymine.org> wrote:
> On Fri, 3 Apr 2009, Simon Riggs wrote:
>>
>> On Fri, 2009-04-03 at 10:04 -0400, Tom Lane wrote:
>>>
>>> Matthew Wakeling <matthew@flymine.org> writes:
>>>>
>>>> On Fri, 3 Apr 2009, Robert Haas wrote:
>>>>>
>>>>> Why not just use SQL to do the join?
>>>>
>>>> Because the merge condition is:
>>>>
>>>> WHERE l1.start <= l2.end AND l2.start <= l1.end
>>>>
>>>> and merge joins in postgres only currently cope with the case where the
>>>> merge condition is an equals relationship.
>>>
>>> I don't actually believe that a standard merge join algorithm will work
>>> with an intransitive join condition ...
>>
>> I think it's a common enough problem that having a non-standard join
>> algorithm written for that case would be interesting indeed.
>
> I'm currently trying to persuade my boss to give me time to do some work to
> implement this in Postgres. It's not something I will be able to start right
> away, but maybe in a little while.
>
> I'm currently seeing this as being able to mark overlap constraints ("&&" in
> quite a few data types) as "OVERLAP_MERGES", and have the planner be able to
> use the new merge join algorithm. So it wouldn't help with the exact query
> above, but would if I rewrote it to use the bioseg or spacial data types'
> overlap operators.
>
> I will need a little help as I am not incredibly familiar with the Postgres
> innards. Would someone be able to do that?

I can help review if you post a patch, even if it's WIP.  But you
should post it to -hackers, not here.

...Robert

Re: plpgsql arrays

From
Matthew Wakeling
Date:
On Fri, 3 Apr 2009, Tom Lane wrote:
>> ... alternatively is there a way to read two results streams
>> simultaneously?
>
> Use two cursors and FETCH from each as needed?  In recent releases you
> can even scroll backwards, which you're going to need to do to make
> a merge join work.

What would be the syntax for putting a single row from a cursor into a
variable? I have tried:

FETCH INTO left left_cursor;

which says syntax error, and

left = FETCH left_cursor;

which gives the error 'ERROR:  missing datatype declaration at or near "="'

Matthew

--
 I've run DOOM more in the last few days than I have the last few
 months.  I just love debugging ;-)  -- Linus Torvalds

Re: plpgsql arrays

From
justin
Date:
Matthew Wakeling wrote:
> What would be the syntax for putting a single row from a cursor into a
> variable? I have tried:
>
> FETCH INTO left left_cursor;
>
> which says syntax error, and
>
> left = FETCH left_cursor;
>
> which gives the error 'ERROR:  missing datatype declaration at or near
> "="'
>
> Matthew
>

Have to declare Left variable  as record data type declaration part of
the function


Re: plpgsql arrays

From
Matthew Wakeling
Date:
On Tue, 7 Apr 2009, justin wrote:
>> What would be the syntax for putting a single row from a cursor into a
>> variable? I have tried:
>>
>> FETCH INTO left left_cursor;
>>
>> which says syntax error, and
>>
>> left = FETCH left_cursor;
>>
>> which gives the error 'ERROR:  missing datatype declaration at or near "="'
>
> Have to declare Left variable  as record data type declaration part of the
> function

It is.

CREATE OR REPLACE FUNCTION overlap_gene_primer() RETURNS SETOF RECORD AS $$
DECLARE
     left location;
     retval RECORD;
BEGIN
     DECLARE left_cursor NO SCROLL CURSOR FOR SELECT location FROM location, gene WHERE location.id = gene.id ORDER BY
objectid,start, end; 
     left = FETCH left_cursor;
END;
$$ LANGUAGE plpgsql;

Matthew

--
"Prove to thyself that all circuits that radiateth and upon which thou worketh
 are grounded, lest they lift thee to high-frequency potential and cause thee
 to radiate also. "             -- The Ten Commandments of Electronics

Re: plpgsql arrays

From
justin
Date:
Matthew Wakeling wrote: <blockquote cite="mid:alpine.DEB.2.00.0904071630420.791@aragorn.flymine.org" type="cite">On
Tue,7 Apr 2009, justin wrote: <br /><blockquote type="cite"><blockquote type="cite">What would be the syntax for
puttinga single row from a cursor into a variable? I have tried: <br /><br /> FETCH INTO left left_cursor; <br /><br />
whichsays syntax error, and <br /><br /> left = FETCH left_cursor; <br /><br /> which gives the error 'ERROR:  missing
datatypedeclaration at or near "="' <br /></blockquote><br /> Have to declare Left variable  as record data type
declarationpart of the function <br /></blockquote><br /> It is. <br /><br /> CREATE OR REPLACE FUNCTION
overlap_gene_primer()RETURNS SETOF RECORD AS $$ <br /> DECLARE <br />     left location; <br />     retval RECORD; <br
/>BEGIN <br />     DECLARE left_cursor NO SCROLL CURSOR FOR SELECT location FROM location, gene WHERE location.id =
gene.idORDER BY objectid, start, end; <br />     left = FETCH left_cursor; <br /> END; <br /> $$ LANGUAGE plpgsql; <br
/><br/> Matthew <br /></blockquote><br /> Change the type to Record<br /> from the help file<br /><code
class="command">FETCH</code>retrieves the next row from the cursor into a target, which might be a <b>row variable, a
recordvariable, or a comma-separated </b>list of simple variables, just like <code class="command">SELECT INTO</code>.
Ifthere is no next row, the target is set to NULL(s). As with <code class="command">SELECT INTO</code>, the special
variable<code class="literal">FOUND</code> can be checked to see whether a row was obtained or not<br /><br /> 

Re: plpgsql arrays

From
Tom Lane
Date:
Matthew Wakeling <matthew@flymine.org> writes:
> CREATE OR REPLACE FUNCTION overlap_gene_primer() RETURNS SETOF RECORD AS $$
> DECLARE
>      left location;
>      retval RECORD;
> BEGIN
>      DECLARE left_cursor NO SCROLL CURSOR FOR SELECT location FROM location, gene WHERE location.id = gene.id ORDER
BYobjectid, start, end; 
>      left = FETCH left_cursor;
> END;
> $$ LANGUAGE plpgsql;

Well, the DECLARE for the cursor should go in the DECLARE section,
and the syntax for the FETCH should be
    FETCH cursorname INTO recordvariablename;
and I'm too lazy to check right now but I think you might be missing
an OPEN for the cursor.

            regards, tom lane

Re: plpgsql arrays

From
Matthew Wakeling
Date:
On Tue, 7 Apr 2009, Tom Lane wrote:
> Matthew Wakeling <matthew@flymine.org> writes:
>> CREATE OR REPLACE FUNCTION overlap_gene_primer() RETURNS SETOF RECORD AS $$
>> DECLARE
>>      left location;
>>      retval RECORD;
>> BEGIN
>>      DECLARE left_cursor NO SCROLL CURSOR FOR SELECT location FROM location, gene WHERE location.id = gene.id ORDER
BYobjectid, start, end; 
>>      left = FETCH left_cursor;
>> END;
>> $$ LANGUAGE plpgsql;
>
> Well, the DECLARE for the cursor should go in the DECLARE section,
> and the syntax for the FETCH should be
>     FETCH cursorname INTO recordvariablename;
> and I'm too lazy to check right now but I think you might be missing
> an OPEN for the cursor.

Yeah, thanks to Justin I found the plpgsql docs for cursors. The main
cursors docs should really link there.

This seems to do what I want:

CREATE OR REPLACE FUNCTION overlap_gene_primer() RETURNS SETOF RECORD AS
$$
DECLARE
     left_cursor NO SCROLL CURSOR FOR SELECT location.* FROM location, gene WHERE location.subjectid = gene.id ORDER BY
objectid,start, end; 
     left location;
BEGIN
     OPEN left_cursor;
     FETCH left_cursor INTO left;
END;
$$ LANGUAGE plpgsql;

Matthew

--
 Lord grant me patience, and I want it NOW!

Re: plpgsql arrays

From
Merlin Moncure
Date:
On Tue, Apr 7, 2009 at 11:18 AM, Matthew Wakeling <matthew@flymine.org> wrote:
> On Fri, 3 Apr 2009, Tom Lane wrote:
>>>
>>> ... alternatively is there a way to read two results streams
>>> simultaneously?
>>
>> Use two cursors and FETCH from each as needed?  In recent releases you
>> can even scroll backwards, which you're going to need to do to make
>> a merge join work.
>
> What would be the syntax for putting a single row from a cursor into a
> variable? I have tried:
>
> FETCH INTO left left_cursor;

according to the docs,

 Examples:

FETCH curs1 INTO rowvar;
FETCH curs2 INTO foo, bar, baz;
FETCH LAST FROM curs3 INTO x, y;
FETCH RELATIVE -2 FROM curs4 INTO x;

http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-USING

merlin

Re: plpgsql arrays

From
Tom Lane
Date:
Merlin Moncure <mmoncure@gmail.com> writes:
> On Tue, Apr 7, 2009 at 11:18 AM, Matthew Wakeling <matthew@flymine.org> wrote:
>> What would be the syntax for putting a single row from a cursor into a
>> variable? I have tried:
>>
>> FETCH INTO left left_cursor;

> according to the docs,
> http://www.postgresql.org/docs/8.3/interactive/plpgsql-cursors.html#PLPGSQL-CURSOR-USING

Subsequent discussion showed that the problem was Matthew hadn't found
that page.  I guess that at least the DECLARE CURSOR reference page
ought to have something like "if you are trying to use cursors in
plpgsql, see <link>".  Matthew, where *were* you looking exactly?

            regards, tom lane

Re: plpgsql arrays

From
Matthew Wakeling
Date:
On Tue, 7 Apr 2009, Tom Lane wrote:
> Subsequent discussion showed that the problem was Matthew hadn't found
> that page.  I guess that at least the DECLARE CURSOR reference page
> ought to have something like "if you are trying to use cursors in
> plpgsql, see <link>".  Matthew, where *were* you looking exactly?

The DECLARE CURSOR page, and then guessing the INTO bit because that's how
SELECT works.

Matthew

--
 for a in past present future; do
   for b in clients employers associates relatives neighbours pets; do
   echo "The opinions here in no way reflect the opinions of my $a $b."
 done; done

Re: plpgsql arrays

From
Tom Lane
Date:
Matthew Wakeling <matthew@flymine.org> writes:
> On Tue, 7 Apr 2009, Tom Lane wrote:
>> Subsequent discussion showed that the problem was Matthew hadn't found
>> that page.  I guess that at least the DECLARE CURSOR reference page
>> ought to have something like "if you are trying to use cursors in
>> plpgsql, see <link>".  Matthew, where *were* you looking exactly?

> The DECLARE CURSOR page, and then guessing the INTO bit because that's how
> SELECT works.

I've added cross-references in the DECLARE and FETCH pages.  I hope
that's sufficient to catch the attention of anyone trying to use cursors
...

            regards, tom lane