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