Subject: regular expression handling in PQ

From: Suguru Yamaguchi <[email-masked]>
Date: Sun, 23 Aug 92 17:40:59 +0900

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="[email-masked]") \g
append person (name="morito", email="[email-masked]") \g
append person (name="youki", email="[email-masked]") \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="[email-masked]") "
APPEND 28238
Go 
* 
Query sent to backend is "append person (name="morito", email="[email-masked]") "
APPEND 28239
Go 
* 
Query sent to backend is "append person (name="youki", email="[email-masked]") "
APPEND 28240
Go 
* 
Query sent to backend is "retrieve (person.all) where person.email ~ "^.*ecip.osaka-u.ac.jp" "
-----------------------------
| name        | email       |
-----------------------------
| suguru      | [email-masked]|
-----------------------------
| morito      | [email-masked]|
-----------------------------
| youki       | [email-masked]|
-----------------------------

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

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

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

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      | [email-masked]|
-----------------------------

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
[email-masked]
Education Center for Information Processing, Osaka University, JAPAN.