Re: Function Issue! - Mailing list pgsql-sql

From Tom Lane
Subject Re: Function Issue!
Date
Msg-id 6771.1092879369@sss.pgh.pa.us
Whole thread Raw
In response to Function Issue!  (Theo Galanakis <Theo.Galanakis@lonelyplanet.com.au>)
List pgsql-sql
Theo Galanakis <Theo.Galanakis@lonelyplanet.com.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


pgsql-sql by date:

Previous
From: Theo Galanakis
Date:
Subject: Function Issue!
Next
From: Josh Berkus
Date:
Subject: Re: multi column foreign key for implicitly unique columns