Обсуждение: Function compile error
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
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
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
Вложения
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 .