On May 26, 2005, at 4:07 PM, Nick Jones wrote:
> I'm trying to figure out how functions work in PGSQL. I've got a
> sample here but I keep getting an error and I'm not sure why.
>
> CREATE FUNCTION test2(VARCHAR) RETURNS SETOF TEXT AS
> '
> DECLARE
> r record;
> BEGIN
> FOR r IN
> select router_name from router where router_name ~ $1
> LOOP
> RETURN NEXT r;
> END LOOP;
> RETURN;
> END;
> ' LANGUAGE 'plpgsql';
>
> Then when I run this command:
> select * from test2('houston');
>
> I get an error:
> WARNING: Error occurred while executing PL/pgSQL function test2
> WARNING: line 7 at return next
> ERROR: Attribute "r" not found
>
> I'm not sure why I get this error, i thought I declared it in the
> declare section.
>
>
r has the structure of table in your select, namely 'router'. Try
RETURN NEXT r.router_name;
John DeSoi, Ph.D.
http://pgedit.com/
Power Tools for PostgreSQL