Re: Function compile error

> -----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