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@joh.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