On Tue, 7 Apr 2009, Tom Lane wrote:
Matthew Wakeling <matthew@xxxxxxxxxxx> 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!
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
