[SQL] PL/SQL block error
Title: Hello, I am very new to the postgres sql, i am trying to execute below pl/sql block in postgres but i am getting error 'ERROR: syntax error at or near "integer" ', Could some one help me in this regard 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='action_pk' and relowner in (select oid from pg_authid where rolname='postgres_ref')) and inx.indrelid in (select oid from pg_class where relname='action' and relowner in (select oid from pg_authid where rolname='postgres_ref')); if v_count = 0 then execute immediate 'create unique index action_pk on action(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 ; Email: [email protected]; URL: www.subexworld.com Disclaimer: This e-mail is bound by the terms and conditions described at http://www.subexworld.com/mail-disclaimer.html
[SQL] Oracle Equivalent queries in Postgres
Title: Hello, I am checking the compatibility of my product with Postgres database and i stucked in forming the below oracle equivalent queries in Postgres database, Could some one help me pleaseee 1) Trying to get index and corresponding columns information of all the tables in mentioned schema select inx.table_name as table_name, inx.index_name as index_name,column_name, case ( when inx.index_type = 'IOT - TOP' then 'Y' else 'N' end, case when inx.uniqueness = 'UNIQUE' then 'Y' when inx.uniqueness = 'NONUNIQUE' then 'N' end, 'N' as ignore_dup_key, cast(inc.column_position as NUMBER(10)) from all_indexes inx, all_ind_columns inc where inx.owner = '" + database.toUpperCase() + "' and inx.table_name = inc.table_name and inx.index_name = inc.index_name and inx.owner = inc.index_owner and inx.owner = inc.table_owner and inx.dropped = 'NO' and inx.table_name = '" + tableName.toUpperCase() + "' order by inx.table_name, inx.index_name, cast(inc.column_position as NUMBER(10)) 2) Trying to get the columns information of all the tables in mentioned schema select tab.TABLE_NAME, col.COLUMN_NAME, col.DATA_TYPE, cast(case when col.CHAR_COL_DECL_LENGTH is NULL then col.DATA_PRECISION else col.CHAR_LENGTH end as NUMBER(10)), cast(col.NULLABLE as CHAR(1)), cast(col.COLUMN_ID as NUMBER(10)) from all_tab_columns col, all_tables tab where tab.TABLE_NAME = col.TABLE_NAME and tab.OWNER = col.OWNER and tab.OWNER = '" + database.toUpperCase() + "' and tab.DROPPED = 'NO' and tab.TABLE_NAME = '" + tableName.toUpperCase() + "' order by tab.TABLE_NAME, cast(col.COLUMN_ID as NUMBER(10)) 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 ; Email: [email protected]; URL: www.subexworld.com Disclaimer: This e-mail is bound by the terms and conditions described at http://www.subexworld.com/mail-disclaimer.html
[SQL] Function compile error
Title: 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 ; Email: [email protected]; URL: www.subexworld.com Disclaimer: This e-mail is bound by the terms and conditions described at http://www.subexworld.com/mail-disclaimer.html
Re: [SQL] Function compile error
Title: 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 ; Email: [email protected]; 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:[email protected]] Sent: Wednesday, February 16, 2011 7:36 AM To: [email protected] 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 ; Email: [email protected] ; URL: www.subexworld.com Disclaimer: This e-mail is bound by the terms and conditions described at 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 .
