Обсуждение: record iteration within 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
"Dan Langille" <dan@langille.org> writes: > I don't under stand why I get this error: > ERROR: record stagingport has no field name Would it help any if the error message used some quotes? ERROR: record "stagingport" has no field "name" ie, there's no column named "name" in SELECT * FROM watch_list_staging ... regards, tom lane
On 25 Feb 2002 at 18:45, Tom Lane wrote: > "Dan Langille" <dan@langille.org> writes: > > I don't under stand why I get this error: > > > ERROR: record stagingport has no field name > > Would it help any if the error message used some quotes? > > ERROR: record "stagingport" has no field "name" > > ie, there's no column named "name" in SELECT * FROM watch_list_staging ... Yes Tom, it would. That makes much more sense now. Thank you. -- Dan Langille The FreeBSD Diary - http://freebsddiary.org/ - practical examples