X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 3D5CCD1E196 for ; Sat, 21 Feb 2004 02:48:15 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 24882-06 for ; Fri, 20 Feb 2004 22:48:17 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id CBC1CD1E1AA for ; Fri, 20 Feb 2004 22:48:13 -0400 (AST) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i1L2mD1Y095375 for ; Sat, 21 Feb 2004 02:48:13 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i1L2Vb1X093957 for pgsql-sql@postgresql.org; Sat, 21 Feb 2004 02:31:37 GMT From: "Greg Patnude" X-Newsgroups: comp.databases.postgresql.sql Subject: User defined types -- Social Security number... Date: Fri, 20 Feb 2004 18:31:00 -0800 Organization: Hub.Org Networking Services Lines: 24 Message-ID: X-Complaints-To: usenet@news.hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2800.1158 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 To: pgsql-sql@postgresql.org X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200402/322 X-Sequence-Number: 17012 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. I imagine that the storage column is probably varchar(11) -- I am looking for a type definition that 1) checks that all numbers are in the range [0..9] 2) inserts the dashes at position 3) accepts either '000123456' OR '000-12-3456' as the input 4) leading zeroes (as in the above) must be stored -- hence varchar(11) instead of numeric 5) always outputs in thje format '000-12-3456' Either that or the question is: How can I coerce postgreSQL into using an input / output "mask"... -- Greg Patnude / The Digital Demention 2916 East Upper Hayden Lake Road Hayden Lake, ID 83835 (208) 762-0762