Skip site navigation (1) Skip section navigation (2)

Re: User defined types -- Social Security number...

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 (view raw or flat)
Thread:
Lists: pgsql-hackerspgsql-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
>



In response to

Responses

pgsql-hackers by date

Next:From: Kevin BrownDate: 2004-02-23 00:59:34
Subject: Re: Too-many-files errors on OS X
Previous:From: Tom LaneDate: 2004-02-22 23:41:40
Subject: Re: [HACKERS] Mac OS X, PostgreSQL, PL/Tcl

pgsql-sql by date

Next:From: Sumita BiswasDate: 2004-02-23 02:54:17
Subject: Re: Postgres DB
Previous:From: Josh BerkusDate: 2004-02-22 19:34:47
Subject: Re: Compiling pl/pgsql functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group