Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 33F39632F9C for ; Tue, 7 Apr 2009 12:45:06 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 11699-03 for ; Tue, 7 Apr 2009 12:45:04 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.emproshunts.com (mail.emproshunts.com [66.64.191.19]) by mail.postgresql.org (Postfix) with ESMTP id 3D58F632CDA for ; Tue, 7 Apr 2009 12:45:04 -0300 (ADT) Received: from [192.168.1.40] by mail.emproshunts.com (GMS 15.02.3689/KX5895.00.ca608199) with ESMTP id klcikaaa for pgsql-performance@postgresql.org; Tue, 7 Apr 2009 11:44:54 -0400 Message-ID: <49DB74FC.9080705@emproshunts.com> Date: Tue, 07 Apr 2009 11:45:00 -0400 From: justin User-Agent: Thunderbird 2.0.0.21 (Windows/20090302) MIME-Version: 1.0 To: Matthew Wakeling CC: Tom Lane , pgsql-performance@postgresql.org Subject: Re: plpgsql arrays References: <28757.1238767715@sss.pgh.pa.us> <49DB700F.8020108@emproshunts.com> In-Reply-To: Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Originating-IP: [192.168.1.40] X-DNSBL: 0 X-AntiSpam: Checked for restricted content by Gordano's AntiSpam Software X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=1.673 tagged_above=0 required=5 tests=HTML_MESSAGE=0.001, MIME_HTML_ONLY=1.672 X-Spam-Level: * X-Archive-Number: 200904/142 X-Sequence-Number: 33509 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;

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

Change the type to Record
from the help file
FETCH retrieves the next row from the cursor into a target, which might be a row variable, a record variable, or a comma-separated list of simple variables, just like SELECT INTO. If there is no next row, the target is set to NULL(s). As with SELECT INTO, the special variable FOUND can be checked to see whether a row was obtained or not