Thread: Function Issue!

<p><font face="Arial" size="2">Can anyone tell me what is wrong with the function below ? </font><br /><font
face="Arial"size="2">It throws an ERROR:  syntax error at or near "FETCH" at character 551</font><p><font face="Arial"
size="2">CREATEOR REPLACE FUNCTION "public"."theo_test2" () RETURNS OPAQUE AS'</font><br /><font face="Arial"
size="2">BEGIN</font><br/><font face="Arial" size="2">   declare curr_theo cursor for select * from
node_names;</font><br/><font face="Arial" size="2">   fetch next from curr_theo;</font><br /><font face="Arial"
size="2">  close curr_theo;</font><br /><font face="Arial" size="2">END;</font><br /><font face="Arial"
size="2">'LANGUAGE'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY DEFINER;</font><br /><p><font face="Arial"
size="2">However,this appears to work :</font><br /><font face="Arial" size="2">begin;</font><br /><font face="Arial"
size="2"> declarecurr_theo cursor for select * from node_names;</font><br /><font face="Arial" size="2"> fetch next
fromcurr_theo;</font><br /><font face="Arial" size="2"> close curr_theo;</font><br /><font face="Arial"
size="2">end;</font><table><tr><tdbgcolor="#ffffff"><font
color="#000000">______________________________________________________________________<br/>This email, including
attachments,is intended only for the addressee<br />and may be confidential, privileged and subject to copyright. If
you<br/>have received this email in error, please advise the sender and delete<br />it. If you are not the intended
recipientof this email, you must not<br />use, copy or disclose its content to anyone. You must not copy or <br
/>communicateto others content that is confidential or subject to <br />copyright, unless you have the consent of the
contentowner.<br /></font></td></tr></table> 

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