От: Matthew Wakeling
Тема: Re: plpgsql arrays
Дата: ,
Msg-id: alpine.DEB.2.00.0904071719320.791@aragorn.flymine.org
(см: обсуждение, исходный текст)
Ответ на: Re: plpgsql arrays  (Tom Lane)
Список: pgsql-performance

Скрыть дерево обсуждения

plpgsql arrays  (Matthew Wakeling, )
 Re: plpgsql arrays  (Robert Haas, )
  Re: plpgsql arrays  (Matthew Wakeling, )
   Re: plpgsql arrays  (Tom Lane, )
    Re: plpgsql arrays  (Matthew Wakeling, )
     Re: plpgsql arrays  (Tom Lane, )
      Re: plpgsql arrays  (Matthew Wakeling, )
       Re: plpgsql arrays  (Matthew Wakeling, )
       Re: plpgsql arrays  (Tom Lane, )
        Re: plpgsql arrays  (Matthew Wakeling, )
        Re: plpgsql arrays  (Nathan Boley, )
    Re: plpgsql arrays  (Simon Riggs, )
     Re: plpgsql arrays  (Alvaro Herrera, )
     Re: plpgsql arrays  (Tom Lane, )
      Re: plpgsql arrays  (Matthew Wakeling, )
     Re: plpgsql arrays  (Matthew Wakeling, )
      Re: plpgsql arrays  (Robert Haas, )
 Re: plpgsql arrays  (Tom Lane, )
  Re: plpgsql arrays  (Matthew Wakeling, )
   Re: plpgsql arrays  (justin, )
    Re: plpgsql arrays  (Matthew Wakeling, )
     Re: plpgsql arrays  (justin, )
     Re: plpgsql arrays  (Tom Lane, )
      Re: plpgsql arrays  (Matthew Wakeling, )
   Re: plpgsql arrays  (Merlin Moncure, )
    Re: plpgsql arrays  (Tom Lane, )
     Re: plpgsql arrays  (Matthew Wakeling, )
      Re: plpgsql arrays  (Tom Lane, )
 Re: plpgsql arrays  (Merlin Moncure, )
  Re: plpgsql arrays  (Merlin Moncure, )
   Re: plpgsql arrays  (Matthew Wakeling, )
    Re: plpgsql arrays  (Merlin Moncure, )

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 = 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!


В списке pgsql-performance по дате сообщения:

От: Tom Lane
Дата:
Сообщение: Re: plpgsql arrays
От: Bruce Momjian
Дата:
Сообщение: Re: 8.4 Performance improvements: was Re: Proposal of tunable fix for scalability of 8.4