Re: plpgsql arrays

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 BY objectid, 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!