When is a type required?

<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Hello,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I’ve been working for months trying to implement a scheme for data encryption.    Only our pii
columnsrequire encryption so it seemed that the manual suggestions implied that we needed to use column level PGP or
the</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">Encrypt/decrypt functions.   For performance reasons we selected encrypt/decrypt.  </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">I’ve hated the choice.  I’ve been plagued by casting between the various datatypes and bytea.  And
areagain dealing with how to decode escape characters.</span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Because most of our loads are from informatica using (odbc or sql copy from external file)  which
can’tdeal with bytea columns, we’ve implemented a scheme where there is a shadow bytea column in addition to the base
column</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">And a before trigger to encrypt on load.  The apps read/write to the base column and the trigger
encryptsand puts the bytea value into the bytea column.  </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Then we need a view for decrypting.  And since some of the apps don’t like to read/write to
differentobjects, rules on the view to enable insert/update. </span></font><p class="MsoNormal"><font face="Arial"
size="2"><spanstyle="font-size:10.0pt; 
font-family:Arial">And we have pii data on dozens to hundreds of tables.   Girr,  the generation functions have given
menightmares. </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Anyway,  starting to look at the long term and 2 things stand out. </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">1.  I need a cast from bytea to the other datatypes</span></font><p class="MsoNormal"><font
face="Arial"size="2"><span style="font-size:10.0pt; 
font-family:Arial">2. use of a user defined type might be a lot easier than managing all of the objects.
</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Has anybody implemented UDTypes and what were the benefits/costs of doing so. </span></font><p
class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; 
font-family:Arial">Thanks</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial">Doug</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt;
font-family:Arial"> </span></font><p class="MsoNormal"><strong><b><font face="Arial" size="2"><span style="font-size:
10.0pt;font-family:Arial">Doug Little</span></font></b></strong><p class="MsoNormal"><font face="Arial" size="2"><span
style="font-size:10.0pt;
font-family:Arial">Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide </span></font><p
class="MsoNormal"style="margin-right:-1.25in"><font face="Arial" size="2"><span
style="font-size:10.0pt;font-family:Arial">500W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax
312.894.5164| Cell 847-997-5741</span></font><h3 style="margin:0in;margin-bottom:.0001pt"><strong><b><font face="Arial"
size="2"><spanstyle="font-size:10.0pt;font-family:Arial;font-weight:normal"><a href="mailto:Douglas.Little@orbitz.com"
title="mailto:dlittle@orbitz.com"><b><span
style="font-weight:bold">Douglas.Little@orbitz.com</span></b></a></span></font></b></strong><span
class="MsoHyperlink"><u><fontcolor="blue"></font></u></span></h3><p><font face="Times New Roman" size="3"><span
style="font-size:12.0pt"> <imgborder="0" height="61" id="_x0000_i1025" src="cid:image002.jpg@01CA6C68.56E901F0"
width="83"/>  <a href="http://www.orbitz.com/" title="http://www.orbitz.com/"><font face="Arial" size="1"><span
style="font-size:
8.0pt;font-family:Arial"><span title="http://www.orbitz.com/">orbitz.com</span></span></font></a> <font
color="blue"><spanstyle="color:blue">|</span></font> <a href="http://www.ebookers.com/"
title="http://www.ebookers.com/"><fontface="Arial" size="1"><span style="font-size:8.0pt;font-family:Arial"><span
title="http://www.ebookers.com/">ebookers.com</span></span></font></a><font color="blue"><span
style="color:blue">|</span></font><a href="http://www.hotelclub.com/" title="http://www.hotelclub.com/"><font
face="Arial"size="1"><span style="font-size:8.0pt;font-family:Arial"><span
title="http://www.hotelclub.com/">hotelclub.com</span></span></font></a><font color="blue"><span
style="color:blue">|</span></font><a href="http://www.cheaptickets.com/" title="http://www.cheaptickets.com/"><font
face="Arial"size="1"><span style="font-size:8.0pt;font-family:Arial"><span
title="http://www.cheaptickets.com/">cheaptickets.com</span></span></font></a><font color="blue"><span
style="color:blue">|</span></font><a href="http://www.ratestogo.com/" title="http://www.ratestogo.com/"><font
face="Arial"size="1"><span style="font-size:8.0pt;font-family:Arial"><span
title="http://www.ratestogo.com/">ratestogo.com</span></span></font></a><fontcolor="blue"><span style="color:blue">
|</span></font><a href="http://www.asiahotels.com/" title="http://www.asiahotels.com/"><font face="Arial"
size="1"><spanstyle="font-size:8.0pt;font-family:Arial"><span
title="http://www.asiahotels.com/">asiahotels.com</span></span></font></a> </span></font><pclass="MsoNormal"><font
face="TimesNew Roman" size="3"><span style="font-size: 
12.0pt"> </span></font></div>