Received: from lists.unixathome.org (lists.unixathome.org [210.48.103.158]) by postgresql.org (Postfix) with ESMTP id 2F5E74763CC for ; Mon, 25 Feb 2002 17:09:05 -0500 (EST) Received: from wocker (lists.unixathome.org [210.48.103.158]) by lists.unixathome.org (8.11.6/8.11.6) with ESMTP id g1PM8wk69093 for ; Tue, 26 Feb 2002 11:08:58 +1300 (NZDT) (envelope-from dan@lists.unixathome.org) Message-Id: <200202252208.g1PM8wk69093@lists.unixathome.org> From: "Dan Langille" Organization: DVL Software Limited To: pgsql-sql@postgresql.org Date: Mon, 25 Feb 2002 17:08:56 -0500 MIME-Version: 1.0 Subject: record iteration within SQL Reply-To: dan@langille.org X-mailer: Pegasus Mail for Windows (v4.01) Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT Content-description: Mail message body X-Archive-Number: 200202/371 X-Sequence-Number: 6793 I'm using the example at http://www.postgresql.org/idocs/index.php?plpgsql- description.html under section "24.2.7.3. Iterating Through Records" as the basis for this code. I don't under stand why I get this error: # select WatchListStagingProcess(1); NOTICE: Error occurred while executing PL/pgSQL function watchliststagingprocess NOTICE: line 8 at SQL statement ERROR: record stagingport has no field name cheers. CREATE FUNCTION WatchListStagingProcess(int4) RETURNS int4 AS ' DECLARE WatchListID ALIAS for $1; stagingport RECORD; BEGIN FOR stagingport IN SELECT * FROM watch_list_staging WHERE watch_list_id = WatchListID LOOP INSERT INTO watch_list_staging_results (watch_list_staging_id, element_id) SELECT stagingport.ID, element.id FROM ports, element WHERE element.name = stagingport.name AND element.id = ports.element_id; END LOOP; END ' LANGUAGE 'plpgsql'; -- Dan Langille The FreeBSD Diary - http://freebsddiary.org/ - practical examples