Thread: 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