From: | "Greg Patnude" <gpatnude(at)hotmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Re: User defined types -- Social Security number... |
Date: | 2004-02-23 00:45:51 |
Message-ID: | c1bih1$1r8$1@news.hub.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers pgsql-sql |
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" <josh(at)agliodbs(dot)com> wrote in message
news:200402221132(dot)50311(dot)josh(at)agliodbs(dot)com(dot)(dot)(dot)
> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Kevin Brown | 2004-02-23 00:59:34 | Re: Too-many-files errors on OS X |
Previous Message | Tom Lane | 2004-02-22 23:41:40 | Re: [HACKERS] Mac OS X, PostgreSQL, PL/Tcl |
From | Date | Subject | |
---|---|---|---|
Next Message | Sumita Biswas | 2004-02-23 02:54:17 | Re: Postgres DB |
Previous Message | Josh Berkus | 2004-02-22 19:34:47 | Re: Compiling pl/pgsql functions |