From: Greg Patnude <gpatnude@hotmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: User defined types -- Social Security number...
Date: Sun, 22 Feb 2004 16:45:51 -0800
Message-ID: <c1bih1$1r8$1@news.hub.org> (raw)
References: <c16fu8$2ro4$1@news.hub.org>
<200402221132.50311.josh@agliodbs.com>
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@agliodbs.com> wrote in message
news:200402221132.50311.josh@agliodbs.com...
> 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
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: gpatnude@hotmail.com
Subject: Re: User defined types -- Social Security number...
In-Reply-To: <c1bih1$1r8$1@news.hub.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox