Re: Functions

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