Re: Function compile error

Thanks Igor Nayman!!! The function worked for me

*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(at)subexworld(dot)com
<mailto:email(dot)id(at)subexworld(dot)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/

On 2/16/2011 9:54 PM, Igor Neyman wrote:
>
>
>> -----Original Message-----
>> From: Sivannarayanreddy [mailto:sivannarayanreddy(at)subexworld(dot)com]
>> Sent: Wednesday, February 16, 2011 7:36 AM
>> To: pgsql-sql(at)postgresql(dot)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(at)subexworld(dot)com
>> <mailto:email(dot)id(at)subexworld(dot)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
> .
>