Re: VARIANT / ANYTYPE datatype

On Wed, May 4, 2011 at 12:36 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Alvaro Herrera <alvherre(at)alvh(dot)no-ip(dot)org> writes:
>> A customer came to us with this request: a way to store "any" data in a
>> column.  We've gone back and forth trying to determine reasonable
>> implementation restrictions, safety and useful semantics for them.
>
> Yes, it seems rather messy.
>
>> The main idea is to be able to store column values in an audit table
>> like this:
>> old_value     variant
>> new_value     variant
>> Currently, they use text for old_value and new_value, but this is, of
>> course, not very satisfactory.
>
> Just out of curiosity, what actual functionality gain would ensue over
> just using text?  It seems like doing anything useful with the audit
> table contents would still require casting the column to text, or the
> moral equivalent of that.

The problem with text is that for composite/table types you lose the
ability to cast back when the structure changes. Most of the
auditing implementations I've done cast new/old to text and drop it
directly into a single column on the audit record. It works ok, but
you have to messily update the text later when the table changes. For
non-variant composites you can add columns down the line and it works
ok in dependent records without too much fuss.

I think though that getting this to work such that type dependency is
via row/column instead of just column is going to be tough. Outside
of composites, I don't see much advantages vs the text route,
performance maybe?

merlin