Thanks Josh -- I understand that there are valid and invalid SSN's --
similar rules apply to zip codes and area codes...
I tried this:
SELECT to_char(123456789, '000-00-0000');
which yields 123-45-6789 -- nicely, I might add...
the trick is getting postgreSQL to do this without having to create an ON
SELECT and ON UPDATE TRIGGER...
an ON UPDATE, SELECT, and INSERT re-write RULE might do the trick...
SSN's CAN in fact start with a leading 0 -- mine does -- "041-xx-xxxx" --
I do agree that there are valid ranges -- my main concern is being able to
store any leading zeros - I just need to make sure that something "looks"
like a valid SSN in the formattig
(nnn-nn-nnnn) and that I can store / retrieve it with the approoriate
format -- what I am really trying to accomplish is an "input mask"...
I hadn't considered using a Domain.... have to look at that....
Greg Patnude / The Digital Demention
2916 East Upper Hayden Lake Road
Hayden Lake, ID 83835
"Josh Berkus" <josh(at)agliodbs(dot)com> wrote in message
> > Anyone have a good pre-built user-defined type definition for creating /
> > maintaining / manipulating a SSN ... where valid chars are in the range
> > 000-00-0000 through 999-99-9999.
> Actually, the range is more narrowly defined than that. I'm not sure of
> exact rules, but you will never see a leading 0 or a -00- in an SSN.
> > I imagine that the storage column is probably varchar(11) -- I am
> > for a type definition that
> Use DOMAINs, not a custom type. It's less work.
> > Either that or the question is: How can I coerce postgreSQL into using
> > input / output "mask"...
> After you've created your DOMAIN, based on the TEXT type, you can overload
> input and output functions to format correctly. Beware, though: input &
> output functions pretty much have to be written in C.
> Josh Berkus
> Aglio Database Solutions
> San Francisco
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
In response to
pgsql-hackers by date
|Next:||From: Kevin Brown||Date: 2004-02-23 00:59:34|
|Subject: Re: Too-many-files errors on OS X|
|Previous:||From: Tom Lane||Date: 2004-02-22 23:41:40|
|Subject: Re: [HACKERS] Mac OS X, PostgreSQL, PL/Tcl |
pgsql-sql by date
|Next:||From: Sumita Biswas||Date: 2004-02-23 02:54:17|
|Subject: Re: Postgres DB|
|Previous:||From: Josh Berkus||Date: 2004-02-22 19:34:47|
|Subject: Re: Compiling pl/pgsql functions|