Thread: plpgsql arrays
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
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
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
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
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
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.
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
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
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
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
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
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
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
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)
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
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
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
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
> 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
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?"
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
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
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
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
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
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 />
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
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!
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
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
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
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