record iteration within SQL

From: "Dan Langille" <dan(at)langille(dot)org>
To: pgsql-sql(at)postgresql(dot)org
Subject: record iteration within SQL
Date: 2002-02-25 22:08:56
Message-ID: 200202252208.g1PM8wk69093@lists.unixathome.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Tom Lane 2002-02-25 23:09:21 Re: Sort problem
Previous Message Jean-Luc Lachance 2002-02-25 21:53:29 Re: Sort problem