Re: Function compile error - Mailing list pgsql-sql

<tt>Thanks Igor Nayman!!! The function worked for me</tt><br /><div class="moz-signature"></div><p
style="margin-bottom:0in;"><strong><font color="#939598"><font face="Arial"><font size="2">Sivannarayanareddy
Nusum</font></font></font></strong><strong><fontcolor="#000080"> </font></strong><strong><font color="#000080"><font
face="Arial"><fontsize="2">| </font></font></font></strong><strong><font color="#0000ff"><font face="Arial"><font
size="2">SystemAnalyst(Moneta GDO)</font></font></font></strong><font color="#000080"> </font><p style="margin-bottom:
0in;"><fontface="Arial"><font size="1" style="font-size: 8pt;"><img align="BOTTOM" border="0" height="71"
name="graphics1"src="cid:part1.09010506.07060500@subexworld.com" width="190" /></font></font><p style="margin-bottom:
0in;"><fontcolor="#808080"><font face="Arial"><font size="1" style="font-size: 8pt;">Subex Limited, Adarsh Tech Park,
OuterRing Road, Devarabisannalli, Bangalore – 560037, India.<br /></font></font></font><font color="#a01e55"><font
face="Arial"><fontsize="1" style="font-size: 8pt;"><b>Phone:</b></font></font></font><font color="#000000">
</font><fontcolor="#000000"><font face="Arial"><font size="1" style="font-size: 8pt;">+91 80 6696 3371;
</font></font></font><fontcolor="#a01e55"><font face="Arial"><font size="1" style="font-size:
8pt;"><b>Mobile:</b></font></font></font><fontcolor="#000000"> </font><font color="#000000"><font face="Arial"><font
size="1"style="font-size: 8pt;">+91 9902065831</font></font></font><font color="#c1272d">  </font><font
color="#a01e55"><fontface="Arial"><font size="1" style="font-size:
8pt;"><b>Fax:</b></font></font></font><fontcolor="#000000"> </font><font color="#000000"><font face="Arial"><font
size="1"style="font-size: 8pt;">+91 80 6696 3333; </font></font></font><p style="margin-bottom: 0in;"><font
color="#a01e55"><fontface="Arial"><font size="1" style="font-size: 8pt;"><b>Email:</b></font></font></font><font
color="#c1272d"> </font><font color="#c1272d"><font face="Arial"><font size="1" style="font-size: 8pt;"><a
href="mailto:email.id@subexworld.com">sivannarayanreddy@subexworld.com</a>;</font></font></font><font
color="#a01e55"><fontface="Arial"><font size="1" style="font-size: 8pt;"><b>URL:</b></font></font></font><font
color="#c1272d"> </font><a href="http://www.subexworld.com/"><font color="#c1272d"><font face="Arial"><font size="1"
style="font-size:              8pt;">www.subexworld.com</font></font></font></a><font color="#c1272d"> </font><p
style="margin-bottom:0in;"><font color="#c1272d"> </font><p class="msonormal"><font color="#808080"><font
face="Arial"><fontsize="1" style="font-size: 8pt;"><i>Disclaimer: This e-mail is bound by the terms and conditions
describedat </i></font></font></font><a href="http://www.subexworld.com/mail-disclaimer.html"><font face="Arial"><font
size="1"style="font-size: 8pt;"><i>http://www.subexworld.com/mail-disclaimer.html</i></font></font></a><font
color="#808080"><br/></font><br /> On 2/16/2011 9:54 PM, Igor Neyman wrote: <blockquote
cite="mid:F4C27E77F7A33E4CA98C19A9DC6722A207221F44@EXCHANGE.corp.perceptron.com"type="cite"><pre wrap="">  

</pre><blockquote type="cite"><pre wrap="">-----Original Message-----
From: Sivannarayanreddy [<a class="moz-txt-link-freetext"
href="mailto:sivannarayanreddy@subexworld.com">mailto:sivannarayanreddy@subexworld.com</a>]
 
Sent: Wednesday, February 16, 2011 7:36 AM
To: <a class="moz-txt-link-abbreviated" href="mailto:pgsql-sql@postgresql.org">pgsql-sql@postgresql.org</a>
Subject: Function compile error

Hello,
I am trying to create the function as below but it is 
throwing error 'ERROR:  syntax error at or near "DECLARE"', 
Could some one help me please

CREATE FUNCTION check_password(databasename text, tablename 
text, indexname text)RETURNS VOID AS 
DECLARE v_count INTEGER;
BEGIN select  count(1) into v_count  from  pg_index inx where  
inx.indexrelid in(select oid from pg_class where relname=$3 and relowner in (select oid from pg_authid where
rolname=$1))and inx.indrelid in (select oid from pg_class where relname=$2 and relowner in (select oid from pg_authid
whererolname=$1)); if v_count = 0 then  
 
execute immediate 'create unique index $3 on $2 (acn_id)';  
end if; 
END;



Sivannarayanareddy Nusum | System Analyst(Moneta GDO) 



Subex Limited, Adarsh Tech Park, Outer Ring Road, 
Devarabisannalli, Bangalore - 560037, India.
Phone: +91 80 6696 3371; Mobile: +91 9902065831  Fax: +91 80 
6696 3333; 

Email:  <a class="moz-txt-link-abbreviated"
href="mailto:sivannarayanreddy@subexworld.com">sivannarayanreddy@subexworld.com</a>
 
<a class="moz-txt-link-rfc2396E" href="mailto:email.id@subexworld.com"><mailto:email.id@subexworld.com></a> ;
URL: <a class="moz-txt-link-abbreviated" href="http://www.subexworld.com">www.subexworld.com</a> 
 
<a class="moz-txt-link-rfc2396E" href="http://www.subexworld.com/"><http://www.subexworld.com/></a>  


Disclaimer: This e-mail is bound by the terms and conditions 
described at <a class="moz-txt-link-freetext"
href="http://www.subexworld.com/mail-disclaimer.html">http://www.subexworld.com/mail-disclaimer.html</a>
 
<a class="moz-txt-link-rfc2396E"
href="http://www.subexworld.com/mail-disclaimer.html"><http://www.subexworld.com/mail-disclaimer.html></a> 
 

</pre></blockquote><pre wrap="">


CREATE FUNCTION check_password(databasename text, tablename text,
indexname text)
RETURNS VOID AS $body$
DECLARE v_count INTEGER;
BEGIN select  count(1) into v_count  from  pg_index inx where
inx.indexrelid in(select oid from pg_class where relname=$3 and relowner in (select oid from pg_authid where
rolname=$1))and inx.indrelid in (select oid from pg_class where relname=$2 and relowner in (select oid from pg_authid
whererolname=$1)); if v_count = 0 then  
 
execute immediate 'create unique index $3 on $2 (acn_id)';  
end if; 
END;
$body$LANGUAGE PLPGSQL;

Regards,
Igor Neyman
.

</pre></blockquote>