Matthew Wakeling 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 ". M
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 ". Matthew, where *were* you looking exact
Merlin Moncure writes:
> On Tue, Apr 7, 2009 at 11:18 AM, 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;
> according to the docs,
> http://www.postgresql.org/docs/8.3/interactive/plp
On Tue, Apr 7, 2009 at 11:18 AM, Matthew Wakeling 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 y
On Tue, 7 Apr 2009, Tom Lane wrote:
Matthew Wakeling 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 = g
Matthew Wakeling 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 BY objectid,
Matthew Wakeling wrote:
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;
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 "="'
H
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
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 synt
On Mon, Apr 6, 2009 at 8:52 AM, Matthew Wakeling wrote:
> On Fri, 3 Apr 2009, Simon Riggs wrote:
>>
>> On Fri, 2009-04-03 at 10:04 -0400, Tom Lane wrote:
>>>
>>> Matthew Wakeling writes:
On Fri, 3 Apr 2009, Robert Haas wrote:
>
> Why not just use SQL to do the join?
Be
On Fri, 3 Apr 2009, Simon Riggs wrote:
On Fri, 2009-04-03 at 10:04 -0400, Tom Lane wrote:
Matthew Wakeling 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
On Fri, 3 Apr 2009, Tom Lane wrote:
Simon Riggs 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
alg
> 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
Simon Riggs 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 w
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
On Fri, 2009-04-03 at 10:04 -0400, Tom Lane wrote:
> Matthew Wakeling 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
On Fri, Apr 3, 2009 at 11:15 AM, Matthew Wakeling 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.
>
>> thi
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 t
On Fri, Apr 3, 2009 at 11:02 AM, Merlin Moncure wrote:
> On Fri, Apr 3, 2009 at 9:32 AM, Matthew Wakeling 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
>>
On Fri, Apr 3, 2009 at 9:32 AM, Matthew Wakeling 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 % 1 = 0) THE
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
t
Matthew Wakeling 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.
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 elem
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 choo
Matthew Wakeling 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 kee
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 t
Matthew Wakeling 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
Matthew Wakeling 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
On Fri, 3 Apr 2009, Robert Haas wrote:
On Fri, Apr 3, 2009 at 9:32 AM, Matthew Wakeling 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
On Fri, Apr 3, 2009 at 9:32 AM, Matthew Wakeling 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
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
afterwa
32 matches
Mail list logo
