Thread: Error passing parameter to functions

<div class="Section1"><p class="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><font face="Courier
New"size="2"><span style="font-size:10.0pt;font-family:"Courier New"; 
mso-ansi-language:EN-US">HI, </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US">Could someone tell me what I am doing wrong please (<span class="SpellE">Postgresql</span>
7.2).<span style="mso-spacerun:yes"> </span>Thanks</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US">The functions I am creating <span class="GramE">is</span>:</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"><span style="mso-spacerun:yes">   </span>CREATE OR REPLACE FUNCTION <span
class="SpellE">getthemedescription</span>(text) RETURNS text <span class="GramE">AS '</span></span></font><p
class="MsoNormal"style="mso-layout-grid-align:none;text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"><span style="mso-spacerun:yes">   </span>DECLARE </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"><span style="mso-tab-count:1">      </span><span class="SpellE"><span
class="GramE">themeid</span></span>ALIAS FOR $1;</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"><span style="mso-tab-count:1">      </span><span class="SpellE"><span
class="GramE">metaid</span></span><span class="SpellE">metadata.meta_id%TYPE</span>;</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"><span style="mso-tab-count:1">      </span><span class="SpellE"><span
class="GramE">generalid</span></span><span class="SpellE">general.general_id%TYPE</span>;</span></font><p
class="MsoNormal"style="mso-layout-grid-align:none;text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"><span style="mso-tab-count:1">      </span><span class="SpellE"><span
class="GramE">descriptionidcursor</span></span>CURSOR FOR SELECT <span class="SpellE">description_id</span> FROM <span
class="SpellE">general_description</span>WHERE <span class="SpellE">general_id</span> = <span
class="SpellE">generalid</span>;</span></font><pclass="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"><span style="mso-spacerun:yes">   </span>BEGIN</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"><span style="mso-tab-count:1">      </span>SELECT INTO <span class="SpellE">metaid</span>
<spanclass="SpellE">meta_id</span> FROM <span class="SpellE">themehasmetadata</span> WHERE <span
class="SpellE">theme_id</span><span class="GramE">=<span style="mso-spacerun:yes">  </span><span
class="SpellE">themeid</span></span>;</span></font><pclass="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"><span style="mso-tab-count:1">      </span>SELECT INTO <span class="SpellE">generalid</span>
<spanclass="SpellE">general_id</span> FROM <span class="SpellE">meta_general</span> WHERE <span
class="SpellE">meta_id</span>= <span class="SpellE">metaid</span>;</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"><span style="mso-tab-count:1">      </span>OPEN <span
class="SpellE">descriptionidcursor</span>;</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"><span style="mso-tab-count:1">      </span>RETURN "<span
class="SpellE">descriptionidcursor</span>"</span></font><pclass="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US">END;</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><spanclass="GramE"><font face="Courier New" size="2"><span
style="font-size:10.0pt;
font-family:"Courier New";mso-ansi-language:EN-US">' LANGUAGE</span></font></span><font face="Courier New"
size="2"><spanstyle="font-size:10.0pt;font-family:"Courier New"; 
mso-ansi-language:EN-US"> '<span class="SpellE">plpgsql</span>';</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US">I call this function in the following way: </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"><span style="mso-tab-count:1">      </span>String <span class="SpellE">themeID</span> =
"mathematics";</span></font><pclass="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><font
face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New"; 
mso-ansi-language:EN-US"><span style="mso-tab-count:1">      </span>String <span class="SpellE">queryString</span> =
"SELECT<span class="SpellE"><span class="GramE">getthemedescription</span></span><span class="GramE">(</span>" + <span
class="SpellE">themeID</span>+ ")");</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"><span style="mso-tab-count:1">      </span>Statement stmt = <span class="SpellE"><span
class="GramE">conn.createStatement</span></span><spanclass="GramE">(</span>);</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"><span style="mso-tab-count:1">      </span><span class="SpellE">ResultSet</span> <span
class="SpellE">l_cursorRSet</span>= <span class="SpellE"><span class="GramE">stmt.executeQuery</span></span><span
class="GramE">(</span><spanclass="SpellE">queryString</span>);</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"><span style="mso-tab-count:4">                        </span>.......</span></font><p
class="MsoNormal"style="mso-layout-grid-align:none;text-autospace:none"><font face="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US">If I send the <span class="SpellE">themeID</span> as above I get: ERROR: Attribute
'mathematics'not found.</span></font><p class="MsoNormal" style="mso-layout-grid-align:none;text-autospace:none"><font
face="CourierNew" size="2"><span style="font-size:10.0pt;font-family:"Courier New"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US">If I send the <span class="SpellE">themeID</span> = "'mathematics'" I get:<span
style="mso-spacerun:yes"> </span><span class="SpellE">fmgr_info</span>: function</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US">24907: cache lookup failed.</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US">I think it has something to do with the quotes, but what?</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US">Best regards</span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US"> </span></font><p class="MsoNormal"
style="mso-layout-grid-align:none;text-autospace:none"><fontface="Courier New" size="2"><span
style="font-size:10.0pt;font-family:"CourierNew"; 
mso-ansi-language:EN-US">Jose Luis</span></font><p class="MsoNormal"><font face="Arial" size="2"><span lang="EN-GB"
style="font-size:
10.0pt;font-family:Arial"> </span></font></div>

"Jose Luis LG" <jlopezgonz@terra.es> writes:
> If I send the themeID as above I get: ERROR: Attribute 'mathematics' not
> found.

As expected.
> If I send the themeID = "'mathematics'" I get:  fmgr_info: function
> 24907: cache lookup failed.

The query syntax is now correct, but there's something broken about your
function definition.  My bet is that the plpgsql call handler function
has been deleted.  Try reinstalling plpgsql --- ie, rerun createlang;
and then recreate your function, since it'll still be pointing at the
old plpgsql OID.
        regards, tom lane

Thanks a lot Tom,

This was exactly the problem.  I was going crazy thinking it was an
error in my code.  Is this a bug in postgresql?


Best regards


Jose Luis

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: martes, 05 de marzo de 2002 17:04
> To: Jose Luis LG
> Cc: pgsql-interfaces@postgresql.org
> Subject: Re: [INTERFACES] Error passing parameter to functions
> 
> "Jose Luis LG" <jlopezgonz@terra.es> writes:
> > If I send the themeID as above I get: ERROR: Attribute 'mathematics'
not
> > found.
> 
> As expected.
> 
> > If I send the themeID = "'mathematics'" I get:  fmgr_info: function
> > 24907: cache lookup failed.
> 
> The query syntax is now correct, but there's something broken about
your
> function definition.  My bet is that the plpgsql call handler function
> has been deleted.  Try reinstalling plpgsql --- ie, rerun createlang;
> and then recreate your function, since it'll still be pointing at the
> old plpgsql OID.
> 
>             regards, tom lane

Thanks a lot Tom,

This was exactly the problem.  I was going crazy thinking it was an
error in my code.  Is this a bug in postgresql?


Best regards


Jose Luis

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: martes, 05 de marzo de 2002 17:04
> To: Jose Luis LG
> Cc: pgsql-interfaces@postgresql.org
> Subject: Re: [INTERFACES] Error passing parameter to functions
> 
> "Jose Luis LG" <jlopezgonz@terra.es> writes:
> > If I send the themeID as above I get: ERROR: Attribute 'mathematics'
not
> > found.
> 
> As expected.
> 
> > If I send the themeID = "'mathematics'" I get:  fmgr_info: function
> > 24907: cache lookup failed.
> 
> The query syntax is now correct, but there's something broken about
your
> function definition.  My bet is that the plpgsql call handler function
> has been deleted.  Try reinstalling plpgsql --- ie, rerun createlang;
> and then recreate your function, since it'll still be pointing at the
> old plpgsql OID.
> 
>             regards, tom lane