> -----Original Message----- > From: Sivannarayanreddy [mailto:sivannarayanre...@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: sivannarayanre...@subexworld.com > <mailto:email...@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 -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
