Thread: missing... at end of sql expression

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I am trying to learn pspgsql but I am not having a lot of luck.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I have written a function but I keep getting the error missing... at end of sql expression. Can
anyonehelp!</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">CREATE FUNCTION exp101 () RETURNS text <span class="GramE">AS '</span></span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">  </span>DECLARE</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">     </span></span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">     </span></span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">     </span>-- Declare a variable to hold book titles and set its
default</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">     </span><span class="GramE">--<span style="mso-spacerun:yes"> 
</span>value</span>to a new line.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"><span style="mso-spacerun:yes">    </span><span class="GramE">text_output</span> TEXT
:=''\n'';</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">     </span></span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">     </span>-- Declare a variable to hold rows from
the</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">     </span>-- <span class="GramE">books</span>
table.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">    </span>--row_data enrollment%ROWTYPE;</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">    </span>--row_dataa student%ROWTYPE;</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">  </span></span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">  </span>BEGIN</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes"> </span></span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">     </span>-- Iterate through the results of a
query.</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">    </span>FOR row_data IN select *<span style="mso-spacerun:yes"> 
</span></span></font><pclass="MsoNormal"><span class="GramE"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">from</span></font></span><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">enrollment, student</span></font><p class="MsoNormal"><span
class="GramE"><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">where</span></font></span><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">enrollment.stdid = cast(student.stdid as char(4)) </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">  </span></span></font><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">Loop</span></font><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"></span></font><pclass="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"><span style="mso-spacerun:yes">  </span></span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"><span style="mso-spacerun:yes">       </span>FOR row_dataa IN select <span
class="GramE">student.stdid,</span> student.lname , student.fname ,student.mi, student.phone , student.major,
advisor.lname,advisor.fname, advisor.mi </span></font><p class="MsoNormal"><span class="GramE"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial">from</span></font></span><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">student, enrollment, advisor</span></font><p class="MsoNormal"><span
class="GramE"><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">where</span></font></span><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">enrollment.stdid = cast(student.stdid as char(4)) and student.advid =
advisor.advidand enrollment.crs = row_data.crs<span style="mso-spacerun:yes">  </span>order by student.lname,
student.fname,student.mi</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes"> </span></span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">Loop</span></font><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial"></span></font><pclass="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"><span style="mso-spacerun:yes">       </span></span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"><span style="mso-spacerun:yes">     </span>-- Insert the title of a matching book into the
text_outputvariable.</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">      </span>text_<span class="GramE">output :</span>= text_output ||
row_data.crs||''\n''; </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"><span style="mso-spacerun:yes">    </span>END </span></font><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">LOOP</span></font><fontface="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">;</span></font><pclass="MsoNormal"><span class="GramE"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial">end</span></font></span><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">loop;</span></font><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"><span style="mso-spacerun:yes">      </span>-- Return the list of books.</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">    </span>RETURN text_output;</span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"><span style="mso-spacerun:yes">  </span>END;</span></font><p class="MsoNormal"><span
class="GramE"><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">'
LANGUAGE</span></font></span><fontface="Arial" size="2"><span style="font-size:10.0pt;font-family:Arial">
'plpgsql';</span></font><p><fontface="Times New Roman" size="3"><span style="font-size:12.0pt"> </span></font></div> 

Justine.Cleary@c1data.com writes:
> I am trying to learn pspgsql but I am not having a lot of luck.
> I have written a function but I keep getting the error missing... at end of
> sql expression. Can anyone help!

What it's really unhappy about is that you failed to declare the FOR
variable; this is causing it to assume that the FOR is the integer
variant (FOR i IN low .. high).

The error message is pretty confusing, I agree.  I think 8.0 will
do better.
        regards, tom lane

On Thu, Nov 18, 2004 at 12:28:03PM -0700, Justine.Cleary@c1data.com wrote:

>     --row_data enrollment%ROWTYPE;
>     --row_dataa student%ROWTYPE;

Your loop variables are commented out.

-- 
Michael Fuhr
http://www.fuhr.org/~mfuhr/