Subject: Searching on seperate words in an attribute

From: Eric P Dean <[email-masked]>
Date: Tue, 16 Aug 1994 12:41:42 CDT

I've been working with POSTGRES for a few weeks after a sucessfull
installation on a DEC 5000/133 running Ultrix 4.3.  My work-group
administrator did most of the installation work as I watched over his
shoulder.  I wonder if someone could tell me if it possible to create an
index that will locate instances by searching for words seperately in a
attribute.  For example:  If I have an attribute "artist = Gogh, Vincent
van" I would like to be able to locate the instance by doing a "retrieve
(xxx.all) where xxx.artist = "gogh"" or "retrieve (xxx.all) where
xxx.artist = "VAN"". I would like searches to be case-insensitive and
have some type of truncation feature.  If this is possible, can you
please tell me where I can find the information in the documentation.   

Many thanks

Eric Dean
Curator, Visual Resources Collection
College of Design, Iowa State University

==============================================================================
   To add/remove yourself to/from the POSTGRES mailing list: send mail with 
   the subject line ADD or DEL to "[email-masked]"

   If this fails, send mail to "[email-masked]" and
   a human will deal with it.  DO NOT post to the "postgres" mailing list.
==============================================================================

---

From: [email-masked]
Date: Tue, 16 Aug 1994 20:19:56 -0400 (EDT)

Postgres has a built-in byte array type (bytea).  My question is how do
I use this type?  For example, let's say I have a small GIF file (or any
other binary data) that I want to store as a byte array.

create images (id =int4, contents=bytea)

append images (id=1234, contents=?????????)

What I really want to know is:

1.) What goes where the "??????????" are?
2.) If this can be done, is the big-endian, little-endian problem
          handled by Postgres or do I have to do it myself? 

Thanks,
Robert

--                                                                        
+------------------------------------------------------------------------+
| Robert Patrick                      _/_/_/_/    _/      _/  _/_/_/_/   |
| n-dim Group                         _/      _/  _/      _/  _/      _/ |
| Engineering Design Research Center  _/      _/  _/      _/  _/      _/ |
| Department of Chemical Engineering  _/      _/  _/      _/  _/      _/ |
| Carnegie Mellon University          _/_/_/_/    _/_/_/_/_/  _/_/_/_/   |
| Pittsburgh, PA  15213               _/   _/     _/      _/  _/         |
| Phone: (412)268-5215                _/    _/    _/      _/  _/         |
| Fax: (412)268-5229                  _/     _/   _/      _/  _/         |
| [email-masked]            _/      _/  _/      _/  _/         |
| [email-masked]                                                   |
| World Wide Web: http://paneer.ndim.edrc.cmu.edu:8888/~rp2y/Home.html   |
+------------------------------------------------------------------------+

---

From: [email-masked] (Paul M. Aoki)
Date: Fri, 19 Aug 94 12:47:07 -0700

> 1.) What goes where the "??????????" are?

in general, if you want to know exactly what the syntax for a type
is, look in src/utils/adt (in this case, varlena.c).  the syntax
for bytea appears to be the same as for strings (characters for
printables, \xxx for non-printables).  i think i would use large
objects instead.

>           handled by Postgres or do I have to do it myself? 

postgres does (almost) no byteswapping anywhere, including the 
network connections.
--
  Paul M. Aoki          |  University of California at Berkeley
  [email-masked]  |  Dept. of EECS, Computer Science Division (#1776) 
                        |  Berkeley, CA 94720-1776

---

From: [email-masked] (Paul M. Aoki)
Date: Fri, 19 Aug 94 16:24:08 -0700

Eric P Dean <[email-masked]> writes:
> If I have an attribute "artist = Gogh, Vincent van" I would like to
> be able to locate the instance by doing a "retrieve (xxx.all) where
> xxx.artist = "gogh"" or "retrieve (xxx.all) where xxx.artist =
> "VAN"". I would like searches to be case-insensitive and have some
> type of truncation feature.

there are some regular expression operators, such as the text regex
operator "~":

	where xxx.artist ~ "gogh"::text

or

	where xxx.artist ~ "[vV][aA][nN]"::text

if you want case insensitivity as opposed to using case-conscious
regular expressions, you would have to hack on the routines in
src/utils/adt/regexp.c.

as with most of the abstract data type stuff, the source is the
ultimate documentation for its behavior.
--
  Paul M. Aoki          |  University of California at Berkeley
  [email-masked]  |  Dept. of EECS, Computer Science Division (#1776) 
                        |  Berkeley, CA 94720-1776