Thread

1. Index on a function(field)

   Gabriel Fernandez <gabi@unica.edu> — 2001-05-11T17:42:02Z

   Hi,
   
   Is it possible to create an index using a function(field) sintaxis ?
   
   For instance:
   
   CREATE  INDEX "i1_cdu" on "cdu" using btree ( substr(cdu_code,1,1)
   "varchar_ops" );
   
   If not, should I alter the table to include a field with the value
   'substr(codigo,1,1)'.
   
   Thanks
   
   Gabi :-)
   
   
   

2. Re: Index on a function(field)

   Mitch Vincent <mitch@venux.net> — 2001-05-11T18:23:38Z

   > Is it possible to create an index using a function(field) sintaxis ?
   
   As far as I know you can -- I have lots of indexes on lower(varchar).. There
   may be limitations though so I'll let someone else have the final word :-)
   
   -Mitch
   
   
   
   

3. Oracle to pg update

   Gilles DAROLD <gilles@darold.net> — 2001-05-11T18:48:59Z

   Hi,
   
   Just to tell you that if you have dowloaded the previous version of
   Ora2pg (tool to convert Oracle database to PostgreSQL) you must
   try the new version. The previous one doesn't work as I expected.
   
   Now I can test it with the Oracle Application DB and so test my
   old poor perl code.
   
   Here what it really does now, and todo :
   
               - Database schema export, with unique, primary and foreign key.
   
               - Grants/privileges export by user and group.
               - Indexes and unique indexes export.
               - Table or view selection (by name and max table) export.
               - Sequence exportation (todo).
               - Predefined function/trigger export (todo)
               - Data export (todo)
               - Sql query converter (todo)
   
   If some of you can test it and give me reports and ideas it will be more
   usefull and less buggy. I will try to do my best to implement sequence
   and trigger the new week.
   
   If anyone know if it's possible to know on witch table/column a sequence
   is associated, couln'd find any tips on it.
   
   New version can be download at :
   http://www.samse.fr/GPL/ora2pg/ora2pg-1.2.tar.gz
   or in /contrib/oracle/ when available.
   
   Bruce can you update it. Thanks...
   
   Regards
   
   Gilles DAROLD
   
   
   

4. Re: Index on a function(field)

   Tom Lane <tgl@sss.pgh.pa.us> — 2001-05-11T19:45:19Z

   Gabriel Fernandez <gabi@unica.edu> writes:
   > Is it possible to create an index using a function(field) sintaxis ?
   
   Yes, but *only* on a function of one or more raw fields.
   
   > CREATE  INDEX "i1_cdu" on "cdu" using btree ( substr(cdu_code,1,1)
   > "varchar_ops" );
   
   This doesn't work because you have some constants floating around in the
   mix.
   
   In theory you can work around this by creating a custom function, say
   firstchar(varchar), that does "substr(x,1,1)", and then writing
   
   CREATE  INDEX "i1_cdu" on "cdu" using btree ( firstchar(cdu_code) );
   
   It's kind of a pain though...
   
   			regards, tom lane
   
   

5. Re: Oracle to pg update

   Bruce Momjian <pgman@candle.pha.pa.us> — 2001-05-13T02:10:19Z

   > New version can be download at :
   > http://www.samse.fr/GPL/ora2pg/ora2pg-1.2.tar.gz
   > or in /contrib/oracle/ when available.
   > 
   > Bruce can you update it. Thanks...
   
   Done in current CVS.  This will not appear in 7.1.X though, just 7.2.
   
   -- 
     Bruce Momjian                        |  http://candle.pha.pa.us
     pgman@candle.pha.pa.us               |  (610) 853-3000
     +  If your life is a hard drive,     |  830 Blythe Avenue
     +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026