Re: count_nulls(VARIADIC "any")

On 2015-08-12 7:23 PM, Greg Stark wrote:
> On Wed, Aug 12, 2015 at 6:18 PM, Marko Tiikkaja <marko(at)joh(dot)to> wrote:
>> Will finish this up for the next CF, unless someone wants to tell me how
>> stupid this idea is before that.
>
> I'm kind of puzzled what kind of schema would need this.

The first example I could find from our schema was specifying the URL
for a Remote Procedure Call. You can either specify a single request
URI, or you can specify the pieces: protocol, host, port, path. So the
constraints look roughly like this:

CHECK ((fulluri IS NULL) <> (protocol IS NULL)),
CHECK ((protocol IS NULL) = ALL(ARRAY[host IS NULL, port IS NULL,
path IS NULL]))

Obviously the second one would be much prettier with count_nulls().

The other example is an OOP inheritance-like schema where an object
could be one of any X number of types. You could write that:

CHECK ((a IS NULL)::int + (b IS NULL)::int + (c IS NULL)::int) = 1)

or just:

CHECK (count_nulls(a,b,c) = 1)

The first example could be redesigned with three tables, but that seems
like a cure worse than the disease.

.m