Skip site navigation (1) Skip section navigation (2)

Re: Function Issue!

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Theo Galanakis <Theo(dot)Galanakis(at)lonelyplanet(dot)com(dot)au>
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Function Issue!
Date: 2004-08-19 01:36:09
Message-ID: 6771.1092879369@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-sql
Theo Galanakis <Theo(dot)Galanakis(at)lonelyplanet(dot)com(dot)au> writes:
> Can anyone tell me what is wrong with the function below ? 

> CREATE OR REPLACE FUNCTION "public"."theo_test2" () RETURNS OPAQUE AS'
> BEGIN
>    declare curr_theo cursor for select * from node_names;
>    fetch next from curr_theo;
>    close curr_theo;
> END;
> 'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;

The DECLARE has to go before the BEGIN:

CREATE OR REPLACE FUNCTION "public"."theo_test2" () RETURNS OPAQUE AS'
DECLARE
   curr_theo cursor for select * from node_names;
BEGIN
   fetch next from curr_theo;
   close curr_theo;
END;
'LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;

I think you are missing an OPEN step too, and the FETCH syntax is wrong
for plpgsql.  Read the plpgsql doc section about using cursors --- it
is not at all identical to what you do in plain SQL.

			regards, tom lane

In response to

pgsql-sql by date

Next:From: Josh BerkusDate: 2004-08-19 16:21:59
Subject: Re: multi column foreign key for implicitly unique columns
Previous:From: Theo GalanakisDate: 2004-08-19 01:10:00
Subject: Function Issue!

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group