Re: Function compile error

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

On 2/16/2011 9:54 PM, Igor Neyman wrote:

-----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 where rolname=$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
.