Re: VARIANT / ANYTYPE datatype

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.

If we did have a concept of ANYTYPE, or really we'd need ANYTYPE[],
it could perhaps be used in pg_statistic, in place of the rather ugly
anyarray hack that's there now. But I note that nothing above the level
of C code can do anything very useful with the contents of pg_statistic,
and I'm not sure that having an official type would change that.

> However, as a middle ground we agreed that we could allow a declared
> variant to store any pinned type without restrictions; those can't be
> changed or dropped so there's no safety concern.

If you're going to accept that there are restrictions, I don't see that
there is a good argument against your thought of a declared "union"
type. At least then it's clear what the restrictions are. I am firmly
against exposing the notion of "pinned" types in any user-visible SQL
semantics, and even more firmly against the idea of creating fundamental
functionality that only works for built-in types and can't be extended
to add-on types.

regards, tom lane