[SQL] Is it possible to get DISTINCT rows from RETURNING clause?

[Gnanakumar]

Hi,

Is it possible to get DISTINCT rows from an UPDATE statement using RETURNING
clause?

"MYTABLE" columns are:
APRIMARYKEYCOLUMN
ABOOLEANCOLUMN
EMAIL
COLUMN1
COLUMN2
COLUMN3

UPDATE using RETURNING clause query:
UPDATE MYTABLE SET ABOOLEANCOLUMN = true FROM MYTEMPTABLE WHERE EMAIL =
MYTEMPTABLE.EMAIL RETURNING EMAIL, COLUMN1, COLUMN2, COLUMN3;

Here in this case, I expect to return distinct rows from these columns:
EMAIL, COLUMN1, COLUMN2, COLUMN3. 

I even tried out some ways of getting distinct rows, but it doesn't work.
Though I can still solve this at application layer, I'm trying to find
whether this could be controlled at query-level.  Any different
ideas/suggestions are appreciated.

Regards,
Gnanam


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] After insert trigger and select

[Rok Jaklič]

 On 02/16/2011 08:46 PM, pasman pasmański wrote:

If I have after insert trigger on some table which updates some data in
that same table, will be the select statement from some other client
executed after all statements in that trigger?


select statement is fired before commit ?

pasman
For example let us say that trigger takes a long time to end. Are all 
statements in trigger executed before select from "outside" if select is 
called somewhere between executing of the trigger?



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] After insert trigger and select

[pasman pasmański]

> If I have after insert trigger on some table which updates some data in
> that same table, will be the select statement from some other client
> executed after all statements in that trigger?
>

select statement is fired before commit ?

pasman

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Oracle Equivalent queries in Postgres

[bricklen]

On Wed, Feb 16, 2011 at 2:42 AM, Sivannarayanreddy <
sivannarayanre...@subexworld.com> wrote:

>  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
>
>
>
There is a sourceforge project that attempts to port the information_schema
to Oracle, though I haven't tried it.

http://sourceforge.net/projects/ora-info-schema/

[SQL] After insert trigger and select

[Rok Jaklič]

Hi.

If I have after insert trigger on some table which updates some data in 
that same table, will be the select statement from some other client 
executed after all statements in that trigger?


Thank you and kind regards,

Rok

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Function compile error

[Igor Neyman]

 

> -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 ; 
> 
> Email:  sivannarayanre...@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 
>   
> 



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

[SQL] Retrieve the column values of a record without knowing the names

[arthur_info]

Hello,

I've got the following function and I want to access the fields values of my
record by index. The problem is that my select is retrieving each record
line with all values and not each one of each row on my view... How can I
solve this problem? 

Thanks in advance.


CREATE FUNCTION fc_teste_tce(aluno integer) RETURNS character varying AS
$BODY$ 
DECLARE
  reg record;
BEGIN
  for reg in execute 'SELECT ARRAY (SELECT vw_teste_tce FROM
estagio.vw_teste_tce where aluno = ''3043'' LIMIT 20) AS campos' loop
for j in 1..array_upper(reg.campos,1) loop
  raise notice 'Field Value: %',reg.campos[j];
end loop;
  end loop;
  return 'ok';
END;  
$BODY$

LANGUAGE plpgsql VOLATILE;


-- 
View this message in context: 
http://postgresql.1045698.n5.nabble.com/Retrieve-the-column-values-of-a-record-without-knowing-the-names-tp3387935p3387935.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Oracle Equivalent queries in Postgres

[Tom Lane]

Thomas Kellerer  writes:
> Pavel Stehule, 16.02.2011 12:20:
>>> Unfortunately they are not the same: Oracle does not support
>>> INFORMATION_SCHEMA

>> sorry, I expected so all mature databases support it.

> Yes, this is really hard to understand.

> I would assume creating the INFORMATION_SCHEMA views based on the existing 
> Oracle views is just a matter of maybe 3-4 days of days work. So it is really 
> not understandable that Oracle does not support this.

> But then they probably don't care - after all it's Oracle.

No, from their point of view it would be actively damaging: providing
standardized views would reduce customer lock-in, by making applications
more portable to other DBMSes.  The pain the OP is feeling is a
marketing advantage, so far as Oracle is concerned.

regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Function compile error

[Pavel Stehule]

Hello

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


2011/2/16 Sivannarayanreddy 

>  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:*  sivannarayanre...@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*
>
<>

[SQL] Function compile error

[Sivannarayanreddy]

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:
   sivannarayanre...@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 


  

Re: [SQL] Oracle Equivalent queries in Postgres

[Thomas Kellerer]

Pavel Stehule, 16.02.2011 12:20:

Unfortunately they are not the same: Oracle does not support
INFORMATION_SCHEMA



sorry, I expected so all mature databases support it.


Yes, this is really hard to understand.

I would assume creating the INFORMATION_SCHEMA views based on the existing 
Oracle views is just a matter of maybe 3-4 days of days work. So it is really 
not understandable that Oracle does not support this.

But then they probably don't care - after all it's Oracle.

Regards
Thomas


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Oracle Equivalent queries in Postgres

[Pavel Stehule]

2011/2/16 Thomas Kellerer :
> Pavel Stehule, 16.02.2011 11:50:
>>
>> Try to use a standardized information_schema instead - these views are
>> same on PostgreSQL and Oracle.
>
> Unfortunately they are not the same: Oracle does not support
> INFORMATION_SCHEMA
>

sorry, I expected so all mature databases support it.

Regards
Pavel

> Regards
> Thomas
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Oracle Equivalent queries in Postgres

[Pavel Stehule]

hello

list of tables
http://www.postgresql.org/docs/current/static/infoschema-tables.html
information about column
http://www.postgresql.org/docs/current/static/infoschema-columns.html

information about indexes - it's not part of ANSI/SQL so you have to look to
pg_index or pg_indexes.

Regards

Pavel Stehule


2011/2/16 Sivannarayanreddy 

>  Hi Pavel,
> In the given link, there are no views which can give information about
> indexes.
>
> Is it possible for you to give me the equivalent queries in postgres?
>
> *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:*  sivannarayanre...@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 4:20 PM, Pavel Stehule wrote:
>
> Hello
>
> PostgreSQL uses a different system tables than Oracle. Try to use a
> standardized information_schema instead - these views are same on PostgreSQL
> and Oracle.
>
> http://www.postgresql.org/docs/current/static/information-schema.html
>
> Regards
>
> Pavel Stehule
>
>
>
>
> 2011/2/16 Sivannarayanreddy 
>
>>  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))
>>  fromall_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))
>>
>>  fromall_tab_columnscol,
>>  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:*  sivannarayanre...@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*
>>
>
>
<><>

Re: [SQL] Oracle Equivalent queries in Postgres

[Thomas Kellerer]

Pavel Stehule, 16.02.2011 11:50:

Try to use a standardized information_schema instead - these views are same on 
PostgreSQL and Oracle.


Unfortunately they are not the same: Oracle does not support INFORMATION_SCHEMA

Regards
Thomas


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Oracle Equivalent queries in Postgres

[Pavel Stehule]

Hello

PostgreSQL uses a different system tables than Oracle. Try to use a
standardized information_schema instead - these views are same on PostgreSQL
and Oracle.

http://www.postgresql.org/docs/current/static/information-schema.html

Regards

Pavel Stehule




2011/2/16 Sivannarayanreddy 

>  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))
>  fromall_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))
>
>  fromall_tab_columnscol,
>  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:*  sivannarayanre...@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*
>
<>

[SQL] Oracle Equivalent queries in Postgres

[Sivannarayanreddy]

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:
   sivannarayanre...@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 


  

Re: [SQL] PL/SQL block error

[Pavel Stehule]

Hello

you should to wrap code to function or inline function everywhere.

psql doesn't support PL/SQL and doesn't support inlined PL/SQL blocks.

Regards

Pavel Stehule

2011/2/16 Sivannarayanreddy 

>  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:*  sivannarayanre...@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*
>

[SQL] PL/SQL block error

[Sivannarayanreddy]

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:
   sivannarayanre...@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