When is a type required?

Hello,
I've been working for months trying to implement a scheme for data encryption. Only our pii columns require encryption so it seemed that the manual suggestions implied that we needed to use column level PGP or the
Encrypt/decrypt functions. For performance reasons we selected encrypt/decrypt.

I've hated the choice. I've been plagued by casting between the various datatypes and bytea. And are again dealing with how to decode escape characters.

Because most of our loads are from informatica using (odbc or sql copy from external file) which can't deal with bytea columns, we've implemented a scheme where there is a shadow bytea column in addition to the base column
And a before trigger to encrypt on load. The apps read/write to the base column and the trigger encrypts and puts the bytea value into the bytea column.

Then we need a view for decrypting. And since some of the apps don't like to read/write to different objects, rules on the view to enable insert/update.
And we have pii data on dozens to hundreds of tables. Girr, the generation functions have given me nightmares.

Anyway, starting to look at the long term and 2 things stand out.
1. I need a cast from bytea to the other datatypes
2. use of a user defined type might be a lot easier than managing all of the objects.

Has anybody implemented UDTypes and what were the benefits/costs of doing so.
Thanks
Doug

Doug Little
Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000 Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741
Douglas(dot)Little(at)orbitz(dot)com<mailto:Douglas(dot)Little(at)orbitz(dot)com>

[cid:image002(dot)jpg(at)01CA6C68(dot)56E901F0] orbitz.com<http://www.orbitz.com/> | ebookers.com<http://www.ebookers.com/> | hotelclub.com<http://www.hotelclub.com/> | cheaptickets.com<http://www.cheaptickets.com/> | ratestogo.com<http://www.ratestogo.com/> | asiahotels.com<http://www.asiahotels.com/>