Resent-From: POSTGRES mailing list <postman@postgres.Berkeley.EDU>
Resent-Message-Id: <199311110258.SAA26632@nobozo.CS.Berkeley.EDU>
Sender: owner-postman@postgres.Berkeley.EDU
From: merlin@violet.berkeley.edu
Date: Wed, 10 Nov 1993 18:58:19 -0800
Message-Id: <199311110258.SAA01756@violet.berkeley.edu>
To: postgres@postgres.Berkeley.EDU
Subject: String Matching and Variable-length Text Arrays
Resent-To: postgres-dist@postgres.Berkeley.EDU
Resent-Date: Wed, 10 Nov 93 18:58:22 -0800
Resent-XMts: smtp

	I have a question about querying variable length
text arrays in Postquel.  I am something of a beginner with query
languages and relational systems, so forgive me if the answer
should be obvious from the documentation (which I *have* read).

	I would like to set up a database to manage information
about my collection of compact discs.  For the purposes of example,
I define a simplified class called DISC as follows:

create DISC (musician = text,
	title = text,
	songs = text[])

Note that the `songs' attribute is a variable-length array of text
strings.

	Now, I would like to be able to issue a query, say,
about which discs have songs with, say, the word "Baby" in them.  I
cannot seem to make the `~' operator work over the array, although
it *will* work if I specify a specific array subscript (that is, I
can retrieve all discs whose second song contains "Baby").
The query I would like to issue should return the titles
"The Great Twenty-Eight", "Made in U.S.A.", "Blows Against
the Empire", and "Bless Its Pointed Little Head".

	I have appended a script of an actual monitor session
on my sample data.  What am I doing wrong?


--------------------------------begin script----------------------------

Script started on Wed Nov 10 18:15:00 1993
[1]% monitor music
Welcome to the C POSTGRES terminal monitor

Go 
*
create DISC (musician = text, title = text,
		songs = text[])
\g

Query sent to backend is "create DISC (musician = text, title = text, 		songs = text[])    "
CREATE
Go 
*

append DISC (musician = "Chuck Berry",
title = "The Great Twenty-Eight",
songs = "{"Maybelline",
	"Oh Baby Doll",
	"Sweet Little Sixteen"}")

append DISC (musician = "Beach Boys",
title = "Made in U.S.A.",
songs = "{"Don't Worry Baby",
	"California Girls",
	"Good Vibrations"}")

append DISC (musician = "Hot Tuna",
title = "Hot Tuna",
songs = "{"Hesitation Blues",
	"Uncle Sam Blues",
	"New Song for the Morning"}")

append DISC (musician = "Jefferson Starship",
title = "Blows Against the Empire",
songs = "{"The Baby Tree",
	"A Child is Coming",
	"Sunrise"}")

append DISC (musician = "Jefferson Airplane",
title = "Volunteers",
songs = "{"Good Shepherd",
	"Turn my Life Down",
	"Wooden Ships"}")

append DISC (musician = "Jefferson Airplane",
title = "Bless Its Pointed Little Head",
songs = "{"Somebody to Love",
	"Fat Angel",
	"Rock Me Baby"}")
\g

Query sent to backend is "append DISC (musician = "Chuck Berry", title = "The Great Twenty-Eight", songs = "{"Maybelline", 	"Oh Baby Doll", 	"Sweet Little Sixteen"}")  append DISC (musician = "Beach Boys", title = "Made in U.S.A.", songs = "{"Don't Worry Baby", 	"California Girls", 	"Good Vibrations"}")  append DISC (musician = "Hot Tuna", title = "Hot Tuna", songs = "{"Hesitation Blues", 	"Uncle Sam Blues", 	"New Song for the Morning"}")  append DISC (musician = "Jefferson Starship", title = "Blows Against the Empire", songs = "{"The Baby Tree", 	"A Child is Coming", 	"Sunrise"}")  append DISC (musician = "Jefferson Airplane", title = "Volunteers", songs = "{"Good Shepherd", 	"Turn my Life Down", 	"Wooden Ships"}")  append DISC (musician = "Jefferson Airplane", title = "Bless Its Pointed Little Head", songs = "{"Somebody to Love", 	"Fat Angel", 	"Rock Me Baby"}")   "
APPEND 305890APPEND 305891APPEND 305892APPEND 305893APPEND 305894APPEND 305895
Go 
* retrieve (DISC.all)
\g

Query sent to backend is "retrieve (DISC.all) "
-------------------------------------------
| musician    | title       | songs       |
-------------------------------------------
| Chuck Berry | The Great Twenty-Eight| {"Maybelline","Oh Baby Doll","Sweet Little Sixteen"}|
-------------------------------------------
| Beach Boys  | Made in U.S.A.| {"Don't Worry Baby","California Girls","Good Vibrations"}|
-------------------------------------------
| Hot Tuna    | Hot Tuna    | {"Hesitation Blues","Uncle Sam Blues","New Song for the Morning"}|
-------------------------------------------
| Jefferson Starship| Blows Against the Empire| {"The Baby Tree","A Child is Coming","Sunrise"}|
-------------------------------------------
| Jefferson Airplane| Volunteers  | {"Good Shepherd","Turn my Life Down","Wooden Ships"}|
-------------------------------------------
| Jefferson Airplane| Bless Its Pointed Little Head| {"Somebody to Love","Fat Angel","Rock Me Baby"}|
-------------------------------------------

Go 
* retrieve (DISC.title)
	where DISC.songs[2] ~ "Baby" \g

Query sent to backend is "retrieve (DISC.title) 	where DISC.songs[2] ~ "Baby" "
---------------
| title       |
---------------
| The Great Twenty-Eight|
---------------

Go 
* retrieve (DISC.title)
	where DISC.songs[] ~ "Baby"
\g

Query sent to backend is "retrieve (DISC.title) 	where DISC.songs[] ~ "Baby" "
WARN:Nov 10 18:19:08:parser: syntax error at or near "]"


Go 
* retrieve (DISC.title)
	where DISC.songs ~ "Baby"
\g

Query sent to backend is "retrieve (DISC.title) 	where DISC.songs ~ "Baby" "
WARN:Nov 10 18:19:30:OperatorDef: no operator ~

Go 
* \q
I live to serve you.
[2]% exit
[3]% 
script done on Wed Nov 10 18:20:05 1993

--------------------------------end script----------------------------