[SQL] Reverse pattern match.

2003-08-18 Thread Moonstruck 
I want to create a table of  regular expression patterns (for assessing
phone numbers), something like:
CREATE TABLE CallType ( pattern varchar primary key,
typevarchar,
rateint4);
INSERT INTO CallType VALUES ('0[3-9]','Interstate Call',50);
INSERT INTO CallType VALUES ('9___','Local Call',25);
INSERT INTO CallType VALUES ('0011__%','International Call',100);

Then determine call types, based on a match, something like:


PhoneNumber := '99116633';
SELECT type, rate FROM CallType where pattern LIKE PhoneNumber;

(Sorry about the pseudo-code), but you get the gist. The query returns a
calltype description and a call rate based on the comparison of the actual
phone-number to a table of RE patterns.

I can't get my head around a way to do this, can anyone help?

Guy




---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [SQL] Reverse pattern match.

2003-08-18 Thread Rod Taylor 
On Mon, 2003-08-18 at 03:05, Moonstruck wrote:
> I want to create a table of  regular expression patterns (for assessing
> phone numbers), something like:
> CREATE TABLE CallType ( pattern varchar primary key,
> typevarchar,
> rateint4);
> INSERT INTO CallType VALUES ('0[3-9]','Interstate Call',50);
> INSERT INTO CallType VALUES ('9___','Local Call',25);
> INSERT INTO CallType VALUES ('0011__%','International Call',100);
> 
> Then determine call types, based on a match, something like:
> 
> 
> PhoneNumber := '99116633';
> SELECT type, rate FROM CallType where pattern LIKE PhoneNumber;

SELECT type, rate FROM CallType WHERE PhoneNumber ~ pattern;



signature.asc
Description: This is a digitally signed message part

Re: [SQL] Reverse pattern match.

2003-08-18 Thread Josh Berkus 
Moonstruck,

> I want to create a table of  regular expression patterns (for assessing
> phone numbers), something like:
> CREATE TABLE CallType ( pattern varchar primary key,
> typevarchar,
> rateint4);
> INSERT INTO CallType VALUES ('0[3-9]','Interstate Call',50);
> INSERT INTO CallType VALUES ('9___','Local Call',25);
> INSERT INTO CallType VALUES ('0011__%','International Call',100);

PostgreSQL supports real Regular Expressions, via the ~ operator.   See "~" 
under "functions and operators", and then consult your favorite book or 
online ressouce on how to compose regexps. 

An example of "is not in 415 area code" would be
phone_no ~ '^415\d{7}' 
which should be "415" at the beginning followed by at least 7 other digits.

(Folks, please correct my regex code if it's bad!)

The disadvantage to this approach is that it cannot be indexed.  

-- 
-Josh Berkus
 Aglio Database Solutions
 San Francisco


---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[SQL] Inheritance or no inheritance, there is a question

2003-08-18 Thread Vernon Smith 


I am working on an application system refinement. There is a user profile table in the 
current system. After the refinement, there are new separated roles, A and B, of the 
users. The role A only has a few valid fields of the original profile table while the 
role B still has the whole profile table. In regarding of the DB scheme design, that 
should be an ideal case of using inheritance. I can have something like

table PROFILE_A ( 
ID int – PK,
...
):

table PROFILE_B {
...
inherits (A)
);

However, there is a problem. There are some multi-valued field tables, such as 
languages the person can speak, associated with the profile table referred by ID in 
the profile table. These tables are still needed for the A and B. But, the ID is not 
accessible from the PROFILE_B. 

What is the best solution for this DB scheme problem? 

p.s. I can't use array data type for the multi-valued fields since they need to be 
workable with a selection statement, nor xml or comma separated format for 
maintainablity concern. 

Thanks,

v.



Get advanced SPAM filtering on Webmail or POP Mail ... Get Lycos Mail!
http://login.mail.lycos.com/r/referral?aid=27005

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [SQL] Inheritance or no inheritance, there is a question

2003-08-18 Thread Josh Berkus 
Vernon,

> What is the best solution for this DB scheme problem?

Have you considered not using inheritance?   As a relational-SQL geek myself, 
I'm not keen on inheritance -- I feel it mucks up the relational model.  Not 
everyone agrees with me, of course.

Personally, I'd suggest the following structure:

Profile A
id Primary Key
detail1
detail2

Profile B
id Primary Key references Profile A ( ID )
detail 3
detail 4
detail 5

Profile Languages
id not null references profile A ( ID )
language id
primary key id, language id

etc.

In this way, Profile B is a child table with a 1:0-1 relationship with Profile 
A.  Multi-value dependancies, like Languages, can be related to either the 
people who belong to the B group (and, by implication, the B group) or the 
people who belong to the A group only.

Want the B group?  SELECT A JOIN B
Want the A group only?  SELECT A EXCEPT B

This is the "relational" way to approach the problem.

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]