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 (208) 762-0762 "Josh Berkus" <[EMAIL PROTECTED]> wrote in message news:[EMAIL PROTECTED] > Greg, > > > 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 the > 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 looking > > 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 an > > input / output "mask"... > > After you've created your DOMAIN, based on the TEXT type, you can overload the > 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 > ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
