Thread: Function compile error
<tt>Hello,<br /> 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<br /><br /> CREATE FUNCTION check_password(databasename text, tablename text, indexnametext)RETURNS VOID AS <br /> DECLARE v_count INTEGER;<br /> BEGIN<br /> select count(1) into v_count from pg_indexinx where inx.indexrelid in<br /> (select oid from pg_class where relname=$3 and relowner in <br /> (select oidfrom pg_authid where rolname=$1)) <br /> and inx.indrelid in <br /> (select oid from pg_class where relname=$2 and relownerin <br /> (select oid from pg_authid where rolname=$1)); <br /> if v_count = 0 then <br /> execute immediate 'createunique index $3 on $2 (acn_id)'; <br /> end if; <br /> END;</tt><br /><div class="moz-signature"><br /></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.04020508.04040903@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>
Hello
please, look to page http://www.postgresql.org/docs/9.0/interactive/plpgsql-porting.html
It can be faster, if you try to read PL/pgSQL documentation first. PL/pgSQL is near PL/SQL, but it is a different language and environment still.
http://www.postgresql.org/docs/9.0/interactive/plpgsql.html
Regards
Pavel Stehule
please, look to page http://www.postgresql.org/docs/9.0/interactive/plpgsql-porting.html
It can be faster, if you try to read PL/pgSQL documentation first. PL/pgSQL is near PL/SQL, but it is a different language and environment still.
http://www.postgresql.org/docs/9.0/interactive/plpgsql.html
Regards
Pavel Stehule
2011/2/16 Sivannarayanreddy <sivannarayanreddy@subexworld.com>
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 where rolname=$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: sivannarayanreddy@subexworld.com; URL: www.subexworld.com
Disclaimer: This e-mail is bound by the terms and conditions described at http://www.subexworld.com/mail-disclaimer.html
Attachment
> -----Original Message----- > From: Sivannarayanreddy [mailto:sivannarayanreddy@subexworld.com] > Sent: Wednesday, February 16, 2011 7:36 AM > To: pgsql-sql@postgresql.org > 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 where rolname=$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: sivannarayanreddy@subexworld.com > <mailto:email.id@subexworld.com> ; URL: www.subexworld.com > <http://www.subexworld.com/> > > > > Disclaimer: This e-mail is bound by the terms and conditions > described at http://www.subexworld.com/mail-disclaimer.html > <http://www.subexworld.com/mail-disclaimer.html> > 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
<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>