Return-Path: pg_adm@postgres.berkeley.edu
Received: by postgres.Berkeley.EDU (5.61/1.29)
	id AA04638; Sun, 23 Aug 92 01:39:34 -0700
Message-Id: <9208230839.AA04638@postgres.Berkeley.EDU>
From: Suguru Yamaguchi <suguru@rd.ecip.osaka-u.ac.jp>
Subject: regular expression handling in PQ
To: postgres@postgres.berkeley.edu
Sender: pg_adm@postgres.berkeley.edu
Date: Sun, 23 Aug 92 17:40:59 +0900
From: Suguru Yamaguchi <suguru@rd.ecip.osaka-u.ac.jp>

I'm using Postgres 4.0 now.  I made a simple shell script shown below:

testdb.sh
---------------------------------------------------------
#!/bin/sh
PATH=/usr/postgres/bin:$PATH
export PATH

createdb foo

monitor foo << END_OF_PQ
create person (name=text, email=text) \g
append person (name="suguru", email="suguru@ecip.osaka-u.ac.jp") \g
append person (name="morito", email="morito@ecip.osaka-u.ac.jp") \g
append person (name="youki", email="youki@ecip.osaka-u.ac.jp") \g
retrieve (person.all) where person.email ~ "^.*ecip.osaka-u.ac.jp" \g
retrieve (person.all) where person.email ~ "^.*ecip.osaka-u.ac.jp$" \g
retrieve (person.all) where person.email ~ ".*ecip.osaka-u.ac.jp$" \g
retrieve (person.all) where person.email ~ "^.*@.*ecip.osaka-u.ac.jp" \g
retrieve (person.all) where person.email ~ "^.*@.*ecip.osaka-u.ac.jp$" \g
retrieve (person.all) where person.email ~ ".*@.*ecip.osaka-u.ac.jp$" \g
END_OF_PQ

exit 0
---------------------------------------------------------

and do it.

---------------------------------------------------------
% ./testdb.sh
Welcome to the C POSTGRES terminal monitor

Go 
* 
Query sent to backend is "create person (name=text, email=text) "
CREATE
Go 
* 
Query sent to backend is "append person (name="suguru", email="suguru@ecip.osaka-u.ac.jp") "
APPEND 28238
Go 
* 
Query sent to backend is "append person (name="morito", email="morito@ecip.osaka-u.ac.jp") "
APPEND 28239
Go 
* 
Query sent to backend is "append person (name="youki", email="youki@ecip.osaka-u.ac.jp") "
APPEND 28240
Go 
* 
Query sent to backend is "retrieve (person.all) where person.email ~ "^.*ecip.osaka-u.ac.jp" "
-----------------------------
| name        | email       |
-----------------------------
| suguru      | suguru@ecip.osaka-u.ac.jp|
-----------------------------
| morito      | morito@ecip.osaka-u.ac.jp|
-----------------------------
| youki       | youki@ecip.osaka-u.ac.jp|
-----------------------------

Go 
* 
Query sent to backend is "retrieve (person.all) where person.email ~ "^.*ecip.osaka-u.ac.jp$" "
-----------------------------
| name        | email       |
-----------------------------
| suguru      | suguru@ecip.osaka-u.ac.jp|
-----------------------------
| morito      | morito@ecip.osaka-u.ac.jp|
-----------------------------

Go 
* 
Query sent to backend is "retrieve (person.all) where person.email ~ ".*ecip.osaka-u.ac.jp$" "
-----------------------------
| name        | email       |
-----------------------------
| suguru      | suguru@ecip.osaka-u.ac.jp|
-----------------------------

Go 
* 
Query sent to backend is "retrieve (person.all) where person.email ~ "^.*@.*ecip.osaka-u.ac.jp" "
-----------------------------
| name        | email       |
-----------------------------
| suguru      | suguru@ecip.osaka-u.ac.jp|
-----------------------------
| morito      | morito@ecip.osaka-u.ac.jp|
-----------------------------
| youki       | youki@ecip.osaka-u.ac.jp|
-----------------------------

Go 
* 
Query sent to backend is "retrieve (person.all) where person.email ~ "^.*@.*ecip.osaka-u.ac.jp$" "
-----------------------------
| name        | email       |
-----------------------------

Go 
* 
Query sent to backend is "retrieve (person.all) where person.email ~ ".*@.*ecip.osaka-u.ac.jp$" "
-----------------------------
| name        | email       |
-----------------------------
| suguru      | suguru@ecip.osaka-u.ac.jp|
-----------------------------

Go 
* I live to serve you.
%
---------------------------------------------------------

I believed all retrieves make same outputs, but they made different
outputs. Are there any restrictions on retrieves with regular
expressions in Postgres? 

Suguru Yamaguchi
suguru@ecip.osaka-u.ac.jp
Education Center for Information Processing, Osaka University, JAPAN.