Pl Pgsql problem - Mailing list pgsql-sql

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="1"><span lang="EN-US" style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">Hi,</span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">I am using 7.1.3 on RedHat Linux 7.2.</span></font><p
class="MsoNormal"><fontface="Arial" size="1"><span lang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">I try to create a trigger on the table, I wrote a function in <span
class="SpellE">plpgsql</span>,which always fail on this <span class="SpellE">sql</span> command:</span></font><p
class="MsoNormal"><fontface="Arial" size="1"><span lang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><span class="GramE"><font
face="Arial"size="1"><span lang="EN-US"
style="font-size:9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">select</span></font></span><fontface="Arial"
size="1"><spanlang="EN-US" style="font-size:9.0pt;mso-bidi-font-size: 
10.0pt;font-family:Arial"> (''UPD'' || int2hex(<span class="SpellE">nextval</span>(''<span
class="SpellE">updid</span>''),8)) as <span class="SpellE">v_updid</span>;</span></font><p class="MsoNormal"><font
face="Arial"size="1"><span lang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">The function is like this:</span></font><p class="MsoNormal"><font
face="Arial"size="1"><span lang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><span class="GramE"><font
face="Arial"size="1"><span lang="EN-US"
style="font-size:9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">begin</span></font></span><fontface="Arial"
size="1"><spanlang="EN-US" style="font-size:9.0pt;mso-bidi-font-size: 
10.0pt;font-family:Arial"></span></font><p class="MsoNormal"><font face="Arial" size="1"><span lang="EN-US"
style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"><span style="mso-spacerun:yes">            </span><span
class="GramE">select</span>(''UPD'' || int2hex(<span class="SpellE">nextval</span>(''<span
class="SpellE">updid</span>''),8)) as <span class="SpellE">v_updid</span>;</span></font><p class="MsoNormal"><font
face="Arial"size="1"><span lang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"><span style="mso-spacerun:yes">            </span><span
class="GramE">if</span>not found then</span></font><p class="MsoNormal"><font face="Arial" size="1"><span lang="EN-US"
style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"><span style="mso-spacerun:yes">                 </span><span
class="GramE">raise</span>exception ''Unable to get <span class="SpellE">updid</span>.'';</span></font><p
class="MsoNormal"><fontface="Arial" size="1"><span lang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"><span style="mso-spacerun:yes">            </span><span
class="GramE">end</span>if;</span></font><p class="MsoNormal"><font face="Arial" size="1"><span lang="EN-US"
style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"><span style="mso-spacerun:yes">        </span><span
class="GramE">end</span>;</span></font><pclass="MsoNormal"><font face="Arial" size="1"><span lang="EN-US"
style="font-size:
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">It <span class="GramE">give</span> me this error when I trigger the
function:</span></font><p class="MsoNormal"><font face="Arial" size="1"><span lang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">NOTICE:<span style="mso-spacerun:yes">  </span>Error occurred while
executingPL/<span class="SpellE">pgSQL</span> function <span
class="SpellE">pg_fct_bf_ins_inf_article</span></span></font><pclass="MsoNormal"><font face="Arial" size="1"><span
lang="EN-US"style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">NOTICE:<span style="mso-spacerun:yes">  </span>line 9 at SQL
statement<spanstyle="mso-tab-count:1">   </span>-- the SQL select command</span></font><p class="MsoNormal"><font
face="Arial"size="1"><span lang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">ERROR:<span style="mso-spacerun:yes">  </span>parser: parse error at
ornear "$1"</span></font><p class="MsoNormal"><font face="Arial" size="1"><span lang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">I can execute the SQL without problem. </span></font><p
class="MsoNormal"><fontface="Arial" size="1"><span lang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"><<</span></font><p class="MsoNormal"><span class="GramE"><font
face="Arial"size="1"><span lang="EN-US"
style="font-size:9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">select</span></font></span><fontface="Arial"
size="1"><spanlang="EN-US" style="font-size:9.0pt;mso-bidi-font-size: 
10.0pt;font-family:Arial"> ('UPD' || int2hex(<span class="SpellE">nextval</span>('<span class="SpellE">updid</span>'),
8))as <span class="SpellE">v_updid</span>;</span></font><p class="MsoNormal"><font face="Arial" size="1"><span
lang="EN-US"style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">>></span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial"
size="1"><spanlang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial">Can anyone help me to see what <span class="GramE">is the problem
here</span>.</span></font><pclass="MsoNormal"><font face="Arial" size="1"><span lang="EN-US" style="font-size: 
9.0pt;mso-bidi-font-size:10.0pt;font-family:Arial"> </span></font><p class="MsoAutoSig"><font face="Times New Roman"
size="2"><spanlang="EN-US" style="font-size:10.5pt;mso-no-proof:yes">Li Hao</span></font><p class="MsoNormal"><font
face="TimesNew Roman" size="3"><span lang="EN-US" style="font-size:12.0pt"> </span></font></div>