subject:"\[HACKERS\] count_nulls\(VARIADIC \"any\"\)"

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-02-08 Thread Thomas Munro

On Tue, Feb 9, 2016 at 9:26 AM, Tom Lane  wrote:
> Thomas Munro  writes:
>> Would num_values be a better name than num_nonnulls?
>
> If "value" is a term that excludes null values, it's news to me.

Ah, right, I was thinking of null as the absence of a value.  But in
fact it is a special value that indicates the absence of a "data
value".  And num_data_values doesn't sound great.

-- 
Thomas Munro
http://www.enterprisedb.com

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-02-08 Thread Thomas Munro

On Fri, Feb 5, 2016 at 5:06 PM, Tom Lane  wrote:
> I wrote:
>> Pavel Stehule  writes:
>>> [ num_nulls_v6.patch ]
>
>> I started looking through this.  It seems generally okay, but I'm not
>> very pleased with the function name "num_notnulls".  I think it would
>> be better as "num_nonnulls", as I see Oleksandr suggested already.
>
> Not hearing any complaints, I pushed it with that change and some other
> cosmetic adjustments.

Would num_values be a better name than num_nonnulls?

-- 
Thomas Munro
http://www.enterprisedb.com


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-02-08 Thread Tom Lane

Thomas Munro  writes:
> Would num_values be a better name than num_nonnulls?

If "value" is a term that excludes null values, it's news to me.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-02-05 Thread Marko Tiikkaja


On 2016-02-05 05:06, Tom Lane wrote:

I wrote:

Pavel Stehule  writes:

[ num_nulls_v6.patch ]



I started looking through this.  It seems generally okay, but I'm not
very pleased with the function name "num_notnulls".  I think it would
be better as "num_nonnulls", as I see Oleksandr suggested already.


Not hearing any complaints, I pushed it with that change and some other
cosmetic adjustments.


Thanks Tom and Pavel and everyone who provided feedback.


.m


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-02-04 Thread Tom Lane

Pavel Stehule  writes:
> [ num_nulls_v6.patch ]

I started looking through this.  It seems generally okay, but I'm not
very pleased with the function name "num_notnulls".  I think it would
be better as "num_nonnulls", as I see Oleksandr suggested already.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-02-04 Thread Tom Lane

I wrote:
> Pavel Stehule  writes:
>> [ num_nulls_v6.patch ]

> I started looking through this.  It seems generally okay, but I'm not
> very pleased with the function name "num_notnulls".  I think it would
> be better as "num_nonnulls", as I see Oleksandr suggested already.

Not hearing any complaints, I pushed it with that change and some other
cosmetic adjustments.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-02-04 Thread Pavel Stehule

Dne 5. 2. 2016 1:33 napsal uživatel "Tom Lane" :
>
> Pavel Stehule  writes:
> > [ num_nulls_v6.patch ]
>
> I started looking through this.  It seems generally okay, but I'm not
> very pleased with the function name "num_notnulls".  I think it would
> be better as "num_nonnulls", as I see Oleksandr suggested already.

I have no problem with it.

Regards

Pavel
>
> regards, tom lane

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-01-26 Thread Marko Tiikkaja


On 25/01/16 19:57, Pavel Stehule wrote:

Marco is a author of this patch, so - Marco, please, send final version of
this patch


I don't really care about the tests.  Can we not use the v5 patch 
already in the thread?  As far as I could tell there were no reviewer's 
comments on it anymore.



.m


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-01-26 Thread Pavel Stehule

2016-01-26 11:42 GMT+01:00 Marko Tiikkaja :

> On 25/01/16 19:57, Pavel Stehule wrote:
>
>> Marco is a author of this patch, so - Marco, please, send final version of
>> this patch
>>
>
> I don't really care about the tests.  Can we not use the v5 patch already
> in the thread?  As far as I could tell there were no reviewer's comments on
> it anymore.
>

It was not my request, but I counted Jim as second reviewer.

So, I'll return back original regress tests. If I remember well, there are
no any other objection, so I'll mark this version as ready for commiter.

1. the patch is rebased against master
2. now warning or errors due compilation
3. all tests are passed
4. the code is simple without side effects and possible negative
performance impacts
6. there was not objections against the design
7. the iteration over null bitmap is used more times in our code, but this
is new special case. We don't need iterate over array elements, so we
should not to use existing array iterators.

I'll mark this patch as ready for commiter

Regards

Pavel


>
> .m
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 9c143b2..23c933f
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 182,188 

  

!Comparison Operators
  
 
  comparison
--- 182,188 

  

!Comparison Functions and Operators
  
 
  comparison
***
*** 191,200 
  
 
  The usual comparison operators are available, shown in .
 
  
!
  Comparison Operators
  
   
--- 191,200 
  
 
  The usual comparison operators are available, shown in .
 
  
!
  Comparison Operators
  
   
***
*** 437,442 
--- 437,479 
 
  -->
  
+   
+ Comparison Functions
+ 
+  
+   
+Function
+Description
+Example
+Example Result
+   
+  
+  
+   
+
+  
+   num_notnulls
+  
+  num_notnulls(VARIADIC "any")
+
+Returns the number of not NULL input arguments
+num_nulls(1, NULL, 2)
+2
+   
+   
+
+  
+   num_nulls
+  
+  num_nulls(VARIADIC "any")
+
+Returns the number of NULL input arguments
+num_nulls(1, NULL, 2)
+1
+   
+  
+ 
+

  

*** table2-mapping
*** 10389,10395 


 The standard comparison operators shown in   are available for
 jsonb, but not for json. They follow the
 ordering rules for B-tree operations outlined at .
--- 10426,10432 


 The standard comparison operators shown in   are available for
 jsonb, but not for json. They follow the
 ordering rules for B-tree operations outlined at .
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
new file mode 100644
index 6a306f3..35810d1
*** a/src/backend/utils/adt/misc.c
--- b/src/backend/utils/adt/misc.c
***
*** 43,48 
--- 43,160 
  
  #define atooid(x)  ((Oid) strtoul((x), NULL, 10))
  
+ /*
+  * Collect info about NULL arguments. Returns true when result values
+  * are valid.
+  */
+ static bool
+ count_nulls(FunctionCallInfo fcinfo,
+ int32 *nargs, int32 *nulls)
+ {
+ 	int32 count = 0;
+ 	int i;
+ 
+ 	if (get_fn_expr_variadic(fcinfo->flinfo))
+ 	{
+ 		ArrayType  *arr;
+ 		int ndims, nitems, *dims;
+ 		bits8 *bitmap;
+ 		int bitmask;
+ 
+ 		/*
+ 		 * When parameter with packed variadic arguments is NULL, we
+ 		 * cannot to identify number of variadic argumens (NULL
+ 		 * or not NULL), then the correct result is NULL. This behave
+ 		 * is consistent with other variadic functions - see concat_internal.
+ 		 */
+ 		if (PG_ARGISNULL(0))
+ 			return false;
+ 
+ 		/*
+ 		 * Non-null argument had better be an array.  We assume that any call
+ 		 * context that could let get_fn_expr_variadic return true will have
+ 		 * checked that a VARIADIC-labeled parameter actually is an array.  So
+ 		 * it should be okay to just Assert that it's an array rather than
+ 		 * doing a full-fledged error check.
+ 		 */
+ 		Assert(OidIsValid(get_base_element_type(get_fn_expr_argtype(fcinfo->flinfo, 0;
+ 
+ 		/* OK, safe to fetch the array value */
+ 		arr = PG_GETARG_ARRAYTYPE_P(0);
+ 
+ 		ndims = ARR_NDIM(arr);
+ 		dims = ARR_DIMS(arr);
+ 		nitems = ArrayGetNItems(ndims, dims);
+ 
+ 		bitmap = ARR_NULLBITMAP(arr);
+ 		if (bitmap)
+ 		{
+ 			bitmask = 1;
+ 
+ 			for (i = 0; i < nitems; i++)
+ 			{
+ if ((*bitmap & bitmask) == 0)
+ 	count++;
+ 
+ bitmask <<= 1;
+ if (bitmask == 0x100)
+ {
+ 	bitmap++;
+ 	bitmask = 1;
+ }
+ 			}
+ 		}
+ 
+ 		*nargs = nitems;
+ 		*nulls = count;
+ 	}
+ 	else
+ 	{
+ 		for (i = 0; i < PG_NARGS(); i++)
+ 		{
+ 			if (PG_ARGISNULL(i))
+ count++;
+ 		}
+ 
+ 		*nargs = PG_NARGS();
+ 		*nulls = count;
+ 	}
+ 
+ 	return true;
+ }

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-01-25 Thread Pavel Stehule

2016-01-22 13:34 GMT+01:00 Jim Nasby :

> On 1/21/16 1:48 PM, Pavel Stehule wrote:
>
>> the form of regress tests is not pretty significant issue. Jim's
>> design is little bit transparent, Marko's is maybe little bit
>> practical. Both has sense from my opinion, and any hasn't
>> significant advantage against other.
>>
>>
>> any possible agreement, how these tests should be designed?
>>
>> simple patch, simple regress tests, so there are no reason for long
>> waiting.
>>
>
> I don't really see how individual tests are more practical (you can still
> cut and paste a table...), but since there's no strong consensus either way
> I'd say it's up to you as author.


Marco is a author of this patch, so - Marco, please, send final version of
this patch

Regards

Pavel


>
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-01-22 Thread Jim Nasby


On 1/21/16 1:48 PM, Pavel Stehule wrote:

the form of regress tests is not pretty significant issue. Jim's
design is little bit transparent, Marko's is maybe little bit
practical. Both has sense from my opinion, and any hasn't
significant advantage against other.


any possible agreement, how these tests should be designed?

simple patch, simple regress tests, so there are no reason for long waiting.


I don't really see how individual tests are more practical (you can 
still cut and paste a table...), but since there's no strong consensus 
either way I'd say it's up to you as author.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-01-21 Thread Pavel Stehule

Hi

2016-01-17 8:43 GMT+01:00 Pavel Stehule :

>
>
> 2016-01-12 17:27 GMT+01:00 Marko Tiikkaja :
>
>> On 03/01/16 22:49, Jim Nasby wrote:
>>
>>> In the unit test, I'd personally prefer just building a table with the
>>> test cases and the expected NULL/NOT NULL results, at least for all the
>>> calls that would fit that paradigm. That should significantly reduce the
>>> size of the test. Not a huge deal though...
>>>
>>
>> I don't really see the point.  "The size of the test" doesn't seem like a
>> worthwhile optimization target, unless the test scripts are somehow really
>> unnecessarily large.
>>
>> Further, if you were developing code related to this, previously you
>> could just copy-paste the defective test case in order to easily reproduce
>> a problem.  But now suddenly you need a ton of different setup.
>>
>> I don't expect to really have a say in this, but I think the tests are
>> now worse than they were before.
>>
>
> the form of regress tests is not pretty significant issue. Jim's design is
> little bit transparent, Marko's is maybe little bit practical. Both has
> sense from my opinion, and any hasn't significant advantage against other.
>

any possible agreement, how these tests should be designed?

simple patch, simple regress tests, so there are no reason for long waiting.

Regards

Pavel


> Regards
>
> Pavel
>
>
>>
>>
>> .m
>>
>
>

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-01-16 Thread Pavel Stehule

2016-01-12 17:27 GMT+01:00 Marko Tiikkaja :

> On 03/01/16 22:49, Jim Nasby wrote:
>
>> In the unit test, I'd personally prefer just building a table with the
>> test cases and the expected NULL/NOT NULL results, at least for all the
>> calls that would fit that paradigm. That should significantly reduce the
>> size of the test. Not a huge deal though...
>>
>
> I don't really see the point.  "The size of the test" doesn't seem like a
> worthwhile optimization target, unless the test scripts are somehow really
> unnecessarily large.
>
> Further, if you were developing code related to this, previously you could
> just copy-paste the defective test case in order to easily reproduce a
> problem.  But now suddenly you need a ton of different setup.
>
> I don't expect to really have a say in this, but I think the tests are now
> worse than they were before.
>

the form of regress tests is not pretty significant issue. Jim's design is
little bit transparent, Marko's is maybe little bit practical. Both has
sense from my opinion, and any hasn't significant advantage against other.

Regards

Pavel


>
>
> .m
>

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-01-12 Thread Marko Tiikkaja


On 03/01/16 22:49, Jim Nasby wrote:

In the unit test, I'd personally prefer just building a table with the
test cases and the expected NULL/NOT NULL results, at least for all the
calls that would fit that paradigm. That should significantly reduce the
size of the test. Not a huge deal though...


I don't really see the point.  "The size of the test" doesn't seem like 
a worthwhile optimization target, unless the test scripts are somehow 
really unnecessarily large.


Further, if you were developing code related to this, previously you 
could just copy-paste the defective test case in order to easily 
reproduce a problem.  But now suddenly you need a ton of different setup.


I don't expect to really have a say in this, but I think the tests are 
now worse than they were before.



.m


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-01-04 Thread Pavel Stehule

Hi

2016-01-04 5:49 GMT+01:00 Jim Nasby :

> On 1/3/16 10:23 PM, Pavel Stehule wrote:
>
>> Hi
>>
>> 2016-01-03 22:49 GMT+01:00 Jim Nasby > >:
>>
>> On 1/3/16 2:37 PM, Pavel Stehule wrote:
>>
>> +   /* num_nulls(VARIADIC NULL) is defined as NULL */
>> +   if (PG_ARGISNULL(0))
>> +   return false;
>>
>>
>> Could you add to the comment explaining why that's the desired
>> behavior?
>>
>>
>> This case should be different than num_nulls(VARIADIC ARRAY[..]) - this
>> situation is really equivalent of missing data and NULL is correct
>> answer. It should not be too clean in num_nulls, but when it is cleaner
>> for num_notnulls. And more, it is consistent with other variadic
>> functions in Postgres: see concat_internal and text_format.
>>
>
> Makes sense, now that you explain it. Which is why I'm thinking it'd be
> good to add that explanation to the comment... ;)
>
>
>> Assert(OidIsValid(get_base_element_type(get_fn_expr_argtype(fcinfo->flinfo,
>> 0;
>>
>>
>> Erm... is that really the way to verify that what you have is an
>> array? ISTM there should be a macro for that somewhere...
>>
>>
>> really, it is. It is used more time. Although I am not against some
>> macro, I don't think so it is necessary. The macro should not be too
>> shorter than this text.
>>
>
> Well, if there's other stuff doing that... would be nice to refactor that
> though.
>
> For brevity and example sake it'd probably be better to just use the
>> normal iterator, unless there's a serious speed difference?
>>
>>
>> The iterator does some memory allocations and some access to type cache.
>> Almost all work of iterator is useless for this case. This code is
>> developed by Marko, but I agree with this design. Using the iterator is
>> big gun for this case. I didn't any performance checks, but it should be
>> measurable  for any varlena arrays.
>>
>
> Makes sense then.
>
>
+ enhanced comment
+ rewritten regress tests

Regards

Pavel


> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 8ef9fce..fd7890e
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 182,188 

  

!Comparison Operators
  
 
  comparison
--- 182,188 

  

!Comparison Functions and Operators
  
 
  comparison
***
*** 191,200 
  
 
  The usual comparison operators are available, shown in .
 
  
!
  Comparison Operators
  
   
--- 191,200 
  
 
  The usual comparison operators are available, shown in .
 
  
!
  Comparison Operators
  
   
***
*** 437,442 
--- 437,479 
 
  -->
  
+   
+ Comparison Functions
+ 
+  
+   
+Function
+Description
+Example
+Example Result
+   
+  
+  
+   
+
+  
+   num_notnulls
+  
+  num_notnulls(VARIADIC "any")
+
+Returns the number of not NULL input arguments
+num_nulls(1, NULL, 2)
+2
+   
+   
+
+  
+   num_nulls
+  
+  num_nulls(VARIADIC "any")
+
+Returns the number of NULL input arguments
+num_nulls(1, NULL, 2)
+1
+   
+  
+ 
+

  

*** table2-mapping
*** 10307,10313 


 The standard comparison operators shown in   are available for
 jsonb, but not for json. They follow the
 ordering rules for B-tree operations outlined at .
--- 10344,10350 


 The standard comparison operators shown in   are available for
 jsonb, but not for json. They follow the
 ordering rules for B-tree operations outlined at .
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
new file mode 100644
index 6a306f3..35810d1
*** a/src/backend/utils/adt/misc.c
--- b/src/backend/utils/adt/misc.c
***
*** 43,48 
--- 43,160 
  
  #define atooid(x)  ((Oid) strtoul((x), NULL, 10))
  
+ /*
+  * Collect info about NULL arguments. Returns true when result values
+  * are valid.
+  */
+ static bool
+ count_nulls(FunctionCallInfo fcinfo,
+ int32 *nargs, int32 *nulls)
+ {
+ 	int32 count = 0;
+ 	int i;
+ 
+ 	if (get_fn_expr_variadic(fcinfo->flinfo))
+ 	{
+ 		ArrayType  *arr;
+ 		int ndims, nitems, *dims;
+ 		bits8 *bitmap;
+ 		int bitmask;
+ 
+ 		/*
+ 		 * When parameter with packed variadic arguments is NULL, we
+ 		 * cannot to identify number of variadic argumens (NULL
+ 		 * or not NULL), then the correct result is NULL. This behave
+ 		 * is consistent with other variadic

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-01-03 Thread Pavel Stehule

2016-01-03 21:37 GMT+01:00 Pavel Stehule :

> Hi
>
>
> 2015-08-12 19:18 GMT+02:00 Marko Tiikkaja :
>
>> Hi,
>>
>> I'd like to suggest $SUBJECT for inclusion in Postgres 9.6.  I'm sure
>> everyone would've found it useful at some point in their lives, and the
>> fact that it can't be properly implemented in any language other than C I
>> think speaks for the fact that we as a project should provide it.
>>
>> A quick and dirty proof of concept (patch attached):
>>
>> =# select count_nulls(null::int, null::text, 17, 'bar');
>>  count_nulls
>> -
>>2
>> (1 row)
>>
>> Its natural habitat would be CHECK constraints, e.g:
>>
>>   CHECK (count_nulls(a,b,c) IN (0, 3))
>>
>> Will finish this up for the next CF, unless someone wants to tell me how
>> stupid this idea is before that.
>>
>>
>> .m
>>
>
> I am sending updated version - support num_nulls and num_notnulls
>

and patch


>
> Regards
>
> Pavel
>
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
new file mode 100644
index 8ef9fce..fd7890e
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 182,188 

  

!Comparison Operators
  
 
  comparison
--- 182,188 

  

!Comparison Functions and Operators
  
 
  comparison
***
*** 191,200 
  
 
  The usual comparison operators are available, shown in .
 
  
!
  Comparison Operators
  
   
--- 191,200 
  
 
  The usual comparison operators are available, shown in .
 
  
!
  Comparison Operators
  
   
***
*** 437,442 
--- 437,479 
 
  -->
  
+   
+ Comparison Functions
+ 
+  
+   
+Function
+Description
+Example
+Example Result
+   
+  
+  
+   
+
+  
+   num_notnulls
+  
+  num_notnulls(VARIADIC "any")
+
+Returns the number of not NULL input arguments
+num_nulls(1, NULL, 2)
+2
+   
+   
+
+  
+   num_nulls
+  
+  num_nulls(VARIADIC "any")
+
+Returns the number of NULL input arguments
+num_nulls(1, NULL, 2)
+1
+   
+  
+ 
+

  

*** table2-mapping
*** 10307,10313 


 The standard comparison operators shown in   are available for
 jsonb, but not for json. They follow the
 ordering rules for B-tree operations outlined at .
--- 10344,10350 


 The standard comparison operators shown in   are available for
 jsonb, but not for json. They follow the
 ordering rules for B-tree operations outlined at .
diff --git a/src/backend/utils/adt/misc.c b/src/backend/utils/adt/misc.c
new file mode 100644
index 6a306f3..873d8f6
*** a/src/backend/utils/adt/misc.c
--- b/src/backend/utils/adt/misc.c
***
*** 43,48 
--- 43,155 
  
  #define atooid(x)  ((Oid) strtoul((x), NULL, 10))
  
+ /*
+  * Collect info about NULL arguments. Returns true when result values
+  * are valid.
+  */
+ static bool
+ count_nulls(FunctionCallInfo fcinfo,
+ int32 *nargs, int32 *nulls)
+ {
+ 	int32 count = 0;
+ 	int i;
+ 
+ 	if (get_fn_expr_variadic(fcinfo->flinfo))
+ 	{
+ 		ArrayType  *arr;
+ 		int ndims, nitems, *dims;
+ 		bits8 *bitmap;
+ 		int bitmask;
+ 
+ 		/* num_nulls(VARIADIC NULL) is defined as NULL */
+ 		if (PG_ARGISNULL(0))
+ 			return false;
+ 
+ 		/*
+ 		 * Non-null argument had better be an array.  We assume that any call
+ 		 * context that could let get_fn_expr_variadic return true will have
+ 		 * checked that a VARIADIC-labeled parameter actually is an array.  So
+ 		 * it should be okay to just Assert that it's an array rather than
+ 		 * doing a full-fledged error check.
+ 		 */
+ 		Assert(OidIsValid(get_base_element_type(get_fn_expr_argtype(fcinfo->flinfo, 0;
+ 
+ 		/* OK, safe to fetch the array value */
+ 		arr = PG_GETARG_ARRAYTYPE_P(0);
+ 
+ 		ndims = ARR_NDIM(arr);
+ 		dims = ARR_DIMS(arr);
+ 		nitems = ArrayGetNItems(ndims, dims);
+ 
+ 		bitmap = ARR_NULLBITMAP(arr);
+ 		if (bitmap)
+ 		{
+ 			bitmask = 1;
+ 
+ 			for (i = 0; i < nitems; i++)
+ 			{
+ if ((*bitmap & bitmask) == 0)
+ 	count++;
+ 
+ bitmask <<= 1;
+ if (bitmask == 0x100)
+ {
+ 	bitmap++;
+ 	bitmask = 1;
+ }
+ 			}
+ 		}
+ 
+ 		*nargs = nitems;
+ 		*nulls = count;
+ 	}
+ 	else
+ 	{
+ 		for (i = 0; i < PG_NARGS(); i++)
+ 		{
+ 			if (PG_ARGISNULL(i))
+ count++;
+ 		}
+ 
+ 		*nargs = PG_NARGS();
+ 		*nulls = count;
+ 	}
+ 
+ 	return true;
+ }
+ 
+ /*
+  * num_nulls()
+  *  Count the number of NULL input arguments
+  */
+ Datum
+ pg_num_nulls(PG_FUNCTION_ARGS)
+ {
+ 	int32 nargs,
+ 		nulls;
+ 
+ 	if (!count_nulls(fcinfo, , ))
+ 		PG_RETURN_NULL();
+ 
+ 	PG_RETURN_INT32(nulls);
+ }
+ 
+ /*
+  * num_notnulls()
+  *  Count the number of not NULL input arguments
+  */
+

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-01-03 Thread Pavel Stehule

Hi

2015-08-12 19:18 GMT+02:00 Marko Tiikkaja :

> Hi,
>
> I'd like to suggest $SUBJECT for inclusion in Postgres 9.6.  I'm sure
> everyone would've found it useful at some point in their lives, and the
> fact that it can't be properly implemented in any language other than C I
> think speaks for the fact that we as a project should provide it.
>
> A quick and dirty proof of concept (patch attached):
>
> =# select count_nulls(null::int, null::text, 17, 'bar');
>  count_nulls
> -
>2
> (1 row)
>
> Its natural habitat would be CHECK constraints, e.g:
>
>   CHECK (count_nulls(a,b,c) IN (0, 3))
>
> Will finish this up for the next CF, unless someone wants to tell me how
> stupid this idea is before that.
>
>
> .m
>

I am sending updated version - support num_nulls and num_notnulls

Regards

Pavel

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-01-03 Thread Jim Nasby


On 1/3/16 2:37 PM, Pavel Stehule wrote:

+   /* num_nulls(VARIADIC NULL) is defined as NULL */
+   if (PG_ARGISNULL(0))
+   return false;


Could you add to the comment explaining why that's the desired behavior?


+   /*
+* Non-null argument had better be an array.  We assume that 
any call
+* context that could let get_fn_expr_variadic return true will 
have
+* checked that a VARIADIC-labeled parameter actually is an 
array.  So
+* it should be okay to just Assert that it's an array rather 
than
+* doing a full-fledged error check.
+*/
+   
Assert(OidIsValid(get_base_element_type(get_fn_expr_argtype(fcinfo->flinfo, 
0;


Erm... is that really the way to verify that what you have is an array? 
ISTM there should be a macro for that somewhere...



+   /* OK, safe to fetch the array value */
+   arr = PG_GETARG_ARRAYTYPE_P(0);
+
+   ndims = ARR_NDIM(arr);
+   dims = ARR_DIMS(arr);
+   nitems = ArrayGetNItems(ndims, dims);
+
+   bitmap = ARR_NULLBITMAP(arr);
+   if (bitmap)
+   {
+   bitmask = 1;
+
+   for (i = 0; i < nitems; i++)
+   {
+   if ((*bitmap & bitmask) == 0)
+   count++;
+
+   bitmask <<= 1;
+   if (bitmask == 0x100)
+   {
+   bitmap++;
+   bitmask = 1;


For brevity and example sake it'd probably be better to just use the 
normal iterator, unless there's a serious speed difference?


In the unit test, I'd personally prefer just building a table with the 
test cases and the expected NULL/NOT NULL results, at least for all the 
calls that would fit that paradigm. That should significantly reduce the 
size of the test. Not a huge deal though...


Also, I don't think anything is testing multiples of whatever value... 
how 'bout change the generate_series CASE statement to >40 instead of <>40?

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-01-03 Thread Pavel Stehule

Hi

2016-01-03 22:49 GMT+01:00 Jim Nasby :

> On 1/3/16 2:37 PM, Pavel Stehule wrote:
>
>> +   /* num_nulls(VARIADIC NULL) is defined as NULL */
>> +   if (PG_ARGISNULL(0))
>> +   return false;
>>
>
> Could you add to the comment explaining why that's the desired behavior?
>

This case should be different than num_nulls(VARIADIC ARRAY[..]) - this
situation is really equivalent of missing data and NULL is correct answer.
It should not be too clean in num_nulls, but when it is cleaner for
num_notnulls. And more, it is consistent with other variadic functions in
Postgres: see concat_internal and text_format.


>
> +   /*
>> +* Non-null argument had better be an array.  We assume
>> that any call
>> +* context that could let get_fn_expr_variadic return
>> true will have
>> +* checked that a VARIADIC-labeled parameter actually is
>> an array.  So
>> +* it should be okay to just Assert that it's an array
>> rather than
>> +* doing a full-fledged error check.
>> +*/
>> +
>>  Assert(OidIsValid(get_base_element_type(get_fn_expr_argtype(fcinfo->flinfo,
>> 0;
>>
>
> Erm... is that really the way to verify that what you have is an array?
> ISTM there should be a macro for that somewhere...
>

really, it is. It is used more time. Although I am not against some macro,
I don't think so it is necessary. The macro should not be too shorter than
this text.


>
> +   /* OK, safe to fetch the array value */
>> +   arr = PG_GETARG_ARRAYTYPE_P(0);
>> +
>> +   ndims = ARR_NDIM(arr);
>> +   dims = ARR_DIMS(arr);
>> +   nitems = ArrayGetNItems(ndims, dims);
>> +
>> +   bitmap = ARR_NULLBITMAP(arr);
>> +   if (bitmap)
>> +   {
>> +   bitmask = 1;
>> +
>> +   for (i = 0; i < nitems; i++)
>> +   {
>> +   if ((*bitmap & bitmask) == 0)
>> +   count++;
>> +
>> +   bitmask <<= 1;
>> +   if (bitmask == 0x100)
>> +   {
>> +   bitmap++;
>> +   bitmask = 1;
>>
>
> For brevity and example sake it'd probably be better to just use the
> normal iterator, unless there's a serious speed difference?
>

The iterator does some memory allocations and some access to type cache.
Almost all work of iterator is useless for this case. This code is
developed by Marko, but I agree with this design. Using the iterator is big
gun for this case. I didn't any performance checks, but it should be
measurable  for any varlena arrays.

Regards

Pavel


> In the unit test, I'd personally prefer just building a table with the
> test cases and the expected NULL/NOT NULL results, at least for all the
> calls that would fit that paradigm. That should significantly reduce the
> size of the test. Not a huge deal though...
>
> Also, I don't think anything is testing multiples of whatever value... how
> 'bout change the generate_series CASE statement to >40 instead of <>40?
> --
> Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
> Experts in Analytics, Data Architecture and PostgreSQL
> Data in Trouble? Get it in Treble! http://BlueTreble.com
>

Re: [HACKERS] count_nulls(VARIADIC "any")

2016-01-03 Thread Jim Nasby


On 1/3/16 10:23 PM, Pavel Stehule wrote:

Hi

2016-01-03 22:49 GMT+01:00 Jim Nasby >:

On 1/3/16 2:37 PM, Pavel Stehule wrote:

+   /* num_nulls(VARIADIC NULL) is defined as NULL */
+   if (PG_ARGISNULL(0))
+   return false;


Could you add to the comment explaining why that's the desired behavior?


This case should be different than num_nulls(VARIADIC ARRAY[..]) - this
situation is really equivalent of missing data and NULL is correct
answer. It should not be too clean in num_nulls, but when it is cleaner
for num_notnulls. And more, it is consistent with other variadic
functions in Postgres: see concat_internal and text_format.


Makes sense, now that you explain it. Which is why I'm thinking it'd be 
good to add that explanation to the comment... ;)



  
Assert(OidIsValid(get_base_element_type(get_fn_expr_argtype(fcinfo->flinfo, 
0;


Erm... is that really the way to verify that what you have is an
array? ISTM there should be a macro for that somewhere...


really, it is. It is used more time. Although I am not against some
macro, I don't think so it is necessary. The macro should not be too
shorter than this text.


Well, if there's other stuff doing that... would be nice to refactor 
that though.



For brevity and example sake it'd probably be better to just use the
normal iterator, unless there's a serious speed difference?


The iterator does some memory allocations and some access to type cache.
Almost all work of iterator is useless for this case. This code is
developed by Marko, but I agree with this design. Using the iterator is
big gun for this case. I didn't any performance checks, but it should be
measurable  for any varlena arrays.


Makes sense then.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2015-11-22 Thread Marko Tiikkaja


On 2015-11-22 21:17, Jim Nasby wrote:

On 11/22/15 11:34 AM, Marko Tiikkaja wrote:

On 2015-11-22 18:29, Jim Nasby wrote:

Only if you know how many columns there already are.

Or does this not work if you hand it a row?


It "works" in the sense that it tells you whether the row is NULL or
not.  I.e. the answer will always be 0 or 1.


Hrm, I was hoping something like count_nulls(complex_type.*) would work.


Nope:

=# select num_nulls((f).*) from (select '(1,2,3)'::foo) ss(f);
ERROR:  row expansion via "*" is not supported here


.m


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2015-11-22 Thread Jim Nasby


On 11/22/15 11:34 AM, Marko Tiikkaja wrote:

On 2015-11-22 18:29, Jim Nasby wrote:

Only if you know how many columns there already are.

Or does this not work if you hand it a row?


It "works" in the sense that it tells you whether the row is NULL or
not.  I.e. the answer will always be 0 or 1.


Hrm, I was hoping something like count_nulls(complex_type.*) would work.

I guess one could always create a wrapper function that does 
count_not_nulls() anyway.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2015-11-22 Thread Jim Nasby


On 11/20/15 11:55 PM, Marko Tiikkaja wrote:

On 2015-11-21 06:52, Jim Nasby wrote:

On 11/20/15 11:12 PM, Marko Tiikkaja wrote:

On 2015-11-21 06:02, I wrote:

Here's a patch implementing this under the name num_nulls().  For
January's CF, of course.


I forgot to update the some references in the documentation.  Fixed in
v3, attached.


I thought there was going to be a not-null equivalent as well? I've
definitely wanted both variations in the past.


I'm not sure that's necessary.  It's quite simple to implement yourself
using the  int - int  operator.


Only if you know how many columns there already are.

Or does this not work if you hand it a row?
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2015-11-22 Thread Marko Tiikkaja


On 2015-11-22 18:29, Jim Nasby wrote:

Only if you know how many columns there already are.

Or does this not work if you hand it a row?


It "works" in the sense that it tells you whether the row is NULL or 
not.  I.e. the answer will always be 0 or 1.



.m


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2015-11-20 Thread Marko Tiikkaja


On 2015-11-21 06:02, I wrote:

Here's a patch implementing this under the name num_nulls().  For
January's CF, of course.


I forgot to update the some references in the documentation.  Fixed in 
v3, attached.



.m
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 182,188 

  

!Comparison Operators
  
 
  comparison
--- 182,188 

  

!Comparison Functions and Operators
  
 
  comparison
***
*** 191,200 
  
 
  The usual comparison operators are available, shown in .
 
  
!
  Comparison Operators
  
   
--- 191,200 
  
 
  The usual comparison operators are available, shown in .
 
  
!
  Comparison Operators
  
   
***
*** 437,442 
--- 437,470 
 
  -->
  
+   
+ Comparison Functions
+ 
+  
+   
+Function
+Description
+Example
+Example Result
+   
+  
+  
+   
+
+  
+   num_nulls
+  
+  num_nulls(VARIADIC "any")
+
+Returns the number of NULL input arguments
+num_nulls(1, NULL, 2)
+1
+   
+  
+ 
+
+ 
+ 

  

***
*** 10307,10313  table2-mapping


 The standard comparison operators shown in   are available for
 jsonb, but not for json. They follow the
 ordering rules for B-tree operations outlined at .
--- 10335,10341 


 The standard comparison operators shown in   are available for
 jsonb, but not for json. They follow the
 ordering rules for B-tree operations outlined at .
*** a/src/backend/utils/adt/misc.c
--- b/src/backend/utils/adt/misc.c
***
*** 45,50 
--- 45,118 
  
  
  /*
+  * num_nulls()
+  *  Count the number of NULL input arguments
+  */
+ Datum
+ pg_num_nulls(PG_FUNCTION_ARGS)
+ {
+ 	int32 count = 0;
+ 	int i;
+ 
+ 	if (get_fn_expr_variadic(fcinfo->flinfo))
+ 	{
+ 		ArrayType  *arr;
+ 		int ndims, nitems, *dims;
+ 		bits8 *bitmap;
+ 		int bitmask;
+ 
+ 		/* Should have just the one argument */
+ 		Assert(PG_NARGS() == 1);
+ 
+ 		/* num_nulls(VARIADIC NULL) is defined as NULL */
+ 		if (PG_ARGISNULL(0))
+ 			PG_RETURN_NULL();
+ 
+ 		/*
+ 		 * Non-null argument had better be an array.  We assume that any call
+ 		 * context that could let get_fn_expr_variadic return true will have
+ 		 * checked that a VARIADIC-labeled parameter actually is an array.  So
+ 		 * it should be okay to just Assert that it's an array rather than
+ 		 * doing a full-fledged error check.
+ 		 */
+ 		Assert(OidIsValid(get_base_element_type(get_fn_expr_argtype(fcinfo->flinfo, 0;
+ 
+ 		/* OK, safe to fetch the array value */
+ 		arr = PG_GETARG_ARRAYTYPE_P(0);
+ 
+ 		ndims = ARR_NDIM(arr);
+ 		dims = ARR_DIMS(arr);
+ 		nitems = ArrayGetNItems(ndims, dims);
+ 
+ 		bitmap = ARR_NULLBITMAP(arr);
+ 		if (!bitmap)
+ 			PG_RETURN_INT32(0);
+ 		bitmask = 1;
+ 
+ 		for (i = 0; i < nitems; i++)
+ 		{
+ 			if ((*bitmap & bitmask) == 0)
+ count++;
+ 
+ 			bitmask <<= 1;
+ 			if (bitmask == 0x100)
+ 			{
+ bitmap++;
+ bitmask = 1;
+ 			}
+ 		}
+ 		PG_RETURN_INT32(count);
+ 	}
+ 
+ 	for (i = 0; i < PG_NARGS(); i++)
+ 	{
+ 		if (PG_ARGISNULL(i))
+ 			count++;
+ 	}
+ 	PG_RETURN_INT32(count);
+ }
+ 
+ /*
   * current_database()
   *	Expose the current database to the user
   */
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***
*** 2963,2968  DESCR("adjust time with time zone precision");
--- 2963,2970 
  
  DATA(insert OID = 2003 (  textanycat	   PGNSP PGUID 14 1 0 0 0 f f f f t f s s 2 0 25 "25 2776" _null_ _null_ _null_ _null_ _null_ "select $1 || $2::pg_catalog.text" _null_ _null_ _null_ ));
  DATA(insert OID = 2004 (  anytextcat	   PGNSP PGUID 14 1 0 0 0 f f f f t f s s 2 0 25 "2776 25" _null_ _null_ _null_ _null_ _null_ "select $1::pg_catalog.text || $2" _null_ _null_ _null_ ));
+ DATA(insert OID = 4400 (  num_nullsPGNSP PGUID 12 1 0 2276 0 f f f f f f i s 1 0 23 "2276" "{2276}" "{v}" _null_ _null_ _null_pg_num_nulls _null_ _null_ _null_ ));
+ DESCR("count the number of NULL input arguments");
  
  DATA(insert OID = 2005 (  bytealike		   PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "17 17" _null_ _null_ _null_ _null_ _null_ bytealike _null_ _null_ _null_ ));
  DATA(insert OID = 2006 (  byteanlike	   PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "17 17" _null_ _null_ _null_ _null_ _null_ byteanlike _null_ _null_ _null_ ));
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***
*** 481,486  extern Datum pg_ls_dir(PG_FUNCTION_ARGS);
--- 481,487 
  extern Datum pg_ls_dir_1arg(PG_FUNCTION_ARGS);
  
  /* misc.c */
+ extern Datum pg_num_nulls(PG_FUNCTION_ARGS);
  extern Datum current_database(PG_FUNCTION_ARGS);
  extern Datum current_query(PG_FUNCTION_ARGS);
  extern Datum pg_cancel_backend(PG_FUNCTION_ARGS);
***

Re: [HACKERS] count_nulls(VARIADIC "any")

2015-11-20 Thread Marko Tiikkaja


On 2015-11-21 06:06, Tom Lane wrote:

Marko Tiikkaja  writes:

Here's a patch implementing this under the name num_nulls().  For
January's CF, of course.


What's this do that "count(*) - count(x)" doesn't?


This is sort of a lateral version of count(x); the input is a list of 
expressions rather than an expression executed over a bunch of input rows.



.m


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2015-11-20 Thread Tom Lane

Marko Tiikkaja  writes:
> Here's a patch implementing this under the name num_nulls().  For 
> January's CF, of course.

What's this do that "count(*) - count(x)" doesn't?

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2015-11-20 Thread Jim Nasby


On 11/20/15 11:12 PM, Marko Tiikkaja wrote:

On 2015-11-21 06:02, I wrote:

Here's a patch implementing this under the name num_nulls().  For
January's CF, of course.


I forgot to update the some references in the documentation.  Fixed in
v3, attached.


I thought there was going to be a not-null equivalent as well? I've 
definitely wanted both variations in the past.

--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC "any")

2015-11-20 Thread Marko Tiikkaja


Hello,

Here's a patch implementing this under the name num_nulls().  For 
January's CF, of course.




.m
*** a/doc/src/sgml/func.sgml
--- b/doc/src/sgml/func.sgml
***
*** 182,188 

  

!Comparison Operators
  
 
  comparison
--- 182,188 

  

!Comparison Functions and Operators
  
 
  comparison
***
*** 194,200 
  linkend="functions-comparison-table">.
 
  
!
  Comparison Operators
  
   
--- 194,200 
  linkend="functions-comparison-table">.
 
  
!
  Comparison Operators
  
   
***
*** 437,442 
--- 437,470 
 
  -->
  
+   
+ Comparison Functions
+ 
+  
+   
+Function
+Description
+Example
+Example Result
+   
+  
+  
+   
+
+  
+   num_nulls
+  
+  num_nulls(VARIADIC "any")
+
+Returns the number of NULL input arguments
+num_nulls(1, NULL, 2)
+1
+   
+  
+ 
+
+ 
+ 

  

*** a/src/backend/utils/adt/misc.c
--- b/src/backend/utils/adt/misc.c
***
*** 45,50 
--- 45,118 
  
  
  /*
+  * num_nulls()
+  *  Count the number of NULL input arguments
+  */
+ Datum
+ pg_num_nulls(PG_FUNCTION_ARGS)
+ {
+ 	int32 count = 0;
+ 	int i;
+ 
+ 	if (get_fn_expr_variadic(fcinfo->flinfo))
+ 	{
+ 		ArrayType  *arr;
+ 		int ndims, nitems, *dims;
+ 		bits8 *bitmap;
+ 		int bitmask;
+ 
+ 		/* Should have just the one argument */
+ 		Assert(PG_NARGS() == 1);
+ 
+ 		/* num_nulls(VARIADIC NULL) is defined as NULL */
+ 		if (PG_ARGISNULL(0))
+ 			PG_RETURN_NULL();
+ 
+ 		/*
+ 		 * Non-null argument had better be an array.  We assume that any call
+ 		 * context that could let get_fn_expr_variadic return true will have
+ 		 * checked that a VARIADIC-labeled parameter actually is an array.  So
+ 		 * it should be okay to just Assert that it's an array rather than
+ 		 * doing a full-fledged error check.
+ 		 */
+ 		Assert(OidIsValid(get_base_element_type(get_fn_expr_argtype(fcinfo->flinfo, 0;
+ 
+ 		/* OK, safe to fetch the array value */
+ 		arr = PG_GETARG_ARRAYTYPE_P(0);
+ 
+ 		ndims = ARR_NDIM(arr);
+ 		dims = ARR_DIMS(arr);
+ 		nitems = ArrayGetNItems(ndims, dims);
+ 
+ 		bitmap = ARR_NULLBITMAP(arr);
+ 		if (!bitmap)
+ 			PG_RETURN_INT32(0);
+ 		bitmask = 1;
+ 
+ 		for (i = 0; i < nitems; i++)
+ 		{
+ 			if ((*bitmap & bitmask) == 0)
+ count++;
+ 
+ 			bitmask <<= 1;
+ 			if (bitmask == 0x100)
+ 			{
+ bitmap++;
+ bitmask = 1;
+ 			}
+ 		}
+ 		PG_RETURN_INT32(count);
+ 	}
+ 
+ 	for (i = 0; i < PG_NARGS(); i++)
+ 	{
+ 		if (PG_ARGISNULL(i))
+ 			count++;
+ 	}
+ 	PG_RETURN_INT32(count);
+ }
+ 
+ /*
   * current_database()
   *	Expose the current database to the user
   */
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***
*** 2963,2968  DESCR("adjust time with time zone precision");
--- 2963,2970 
  
  DATA(insert OID = 2003 (  textanycat	   PGNSP PGUID 14 1 0 0 0 f f f f t f s s 2 0 25 "25 2776" _null_ _null_ _null_ _null_ _null_ "select $1 || $2::pg_catalog.text" _null_ _null_ _null_ ));
  DATA(insert OID = 2004 (  anytextcat	   PGNSP PGUID 14 1 0 0 0 f f f f t f s s 2 0 25 "2776 25" _null_ _null_ _null_ _null_ _null_ "select $1::pg_catalog.text || $2" _null_ _null_ _null_ ));
+ DATA(insert OID = 4400 (  num_nullsPGNSP PGUID 12 1 0 2276 0 f f f f f f i s 1 0 23 "2276" "{2276}" "{v}" _null_ _null_ _null_pg_num_nulls _null_ _null_ _null_ ));
+ DESCR("count the number of NULL input arguments");
  
  DATA(insert OID = 2005 (  bytealike		   PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "17 17" _null_ _null_ _null_ _null_ _null_ bytealike _null_ _null_ _null_ ));
  DATA(insert OID = 2006 (  byteanlike	   PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 16 "17 17" _null_ _null_ _null_ _null_ _null_ byteanlike _null_ _null_ _null_ ));
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***
*** 481,486  extern Datum pg_ls_dir(PG_FUNCTION_ARGS);
--- 481,487 
  extern Datum pg_ls_dir_1arg(PG_FUNCTION_ARGS);
  
  /* misc.c */
+ extern Datum pg_num_nulls(PG_FUNCTION_ARGS);
  extern Datum current_database(PG_FUNCTION_ARGS);
  extern Datum current_query(PG_FUNCTION_ARGS);
  extern Datum pg_cancel_backend(PG_FUNCTION_ARGS);
*** /dev/null
--- b/src/test/regress/expected/misc_functions.out
***
*** 0 
--- 1,68 
+ --
+ -- num_nulls()
+ --
+ SELECT num_nulls();
+ ERROR:  function num_nulls() does not exist
+ LINE 1: SELECT num_nulls();
+^
+ HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
+ SELECT num_nulls(NULL);
+  num_nulls 
+ ---
+  1
+ (1 row)
+ 
+ SELECT num_nulls('1');
+  num_nulls 
+ ---
+  0
+ (1 row)
+ 
+ SELECT num_nulls(NULL::text);
+  num_nulls 
+ ---
+  1
+ (1 row)
+ 
+

Re: [HACKERS] count_nulls(VARIADIC "any")

2015-11-20 Thread Marko Tiikkaja


On 2015-11-21 06:52, Jim Nasby wrote:

On 11/20/15 11:12 PM, Marko Tiikkaja wrote:

On 2015-11-21 06:02, I wrote:

Here's a patch implementing this under the name num_nulls().  For
January's CF, of course.


I forgot to update the some references in the documentation.  Fixed in
v3, attached.


I thought there was going to be a not-null equivalent as well? I've
definitely wanted both variations in the past.


I'm not sure that's necessary.  It's quite simple to implement yourself 
using the  int - int  operator.



.m


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC any)

2015-08-13 Thread Pavel Stehule

2015-08-13 9:47 GMT+02:00 Shulgin, Oleksandr oleksandr.shul...@zalando.de:

 On Thu, Aug 13, 2015 at 9:25 AM, Pavel Stehule pavel.steh...@gmail.com
 wrote:



 2015-08-13 9:21 GMT+02:00 Marko Tiikkaja ma...@joh.to:

 On 8/13/15 9:18 AM, Shulgin, Oleksandr wrote:

 nnulls()


 I think I'd prefer num_nulls() over that.


 can be

 what about similar twin function num_nonulls()?


 Yes.  But I'm can't see any precedent for naming it like num_*...  And if
 anything, should it be num_nonnulls() then?


it is detail -  depends on final naming convention.

Re: [HACKERS] count_nulls(VARIADIC any)

2015-08-13 Thread Pavel Stehule

2015-08-13 9:21 GMT+02:00 Marko Tiikkaja ma...@joh.to:

 On 8/13/15 9:18 AM, Shulgin, Oleksandr wrote:

 nnulls()


 I think I'd prefer num_nulls() over that.


can be

what about similar twin function num_nonulls()?

Pavel



 .m



 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC any)

2015-08-13 Thread Shulgin, Oleksandr

On Thu, Aug 13, 2015 at 9:25 AM, Pavel Stehule pavel.steh...@gmail.com
wrote:



 2015-08-13 9:21 GMT+02:00 Marko Tiikkaja ma...@joh.to:

 On 8/13/15 9:18 AM, Shulgin, Oleksandr wrote:

 nnulls()


 I think I'd prefer num_nulls() over that.


 can be

 what about similar twin function num_nonulls()?


Yes.  But I'm can't see any precedent for naming it like num_*...  And if
anything, should it be num_nonnulls() then?

Re: [HACKERS] count_nulls(VARIADIC any)

2015-08-13 Thread Marko Tiikkaja


On 8/13/15 9:18 AM, Shulgin, Oleksandr wrote:

nnulls()


I think I'd prefer num_nulls() over that.


.m


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC any)

2015-08-13 Thread Atri Sharma

On Thu, Aug 13, 2015 at 12:55 PM, Pavel Stehule pavel.steh...@gmail.com
wrote:



 2015-08-13 9:21 GMT+02:00 Marko Tiikkaja ma...@joh.to:

 On 8/13/15 9:18 AM, Shulgin, Oleksandr wrote:

 nnulls()


 I think I'd prefer num_nulls() over that.


 can be

 what about similar twin function num_nonulls()?


+1

Re: [HACKERS] count_nulls(VARIADIC any)

2015-08-13 Thread Shulgin, Oleksandr

On Thu, Aug 13, 2015 at 2:19 AM, David G. Johnston 
david.g.johns...@gmail.com wrote:

 On Wed, Aug 12, 2015 at 4:11 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Peter Geoghegan p...@heroku.com writes:
  On Wed, Aug 12, 2015 at 10:30 AM, Alvaro Herrera
  alvhe...@2ndquadrant.com wrote:
  The name count_nulls() suggest an aggregate function to me, though.

  I thought the same.

 Ditto.  I'd be fine with this if we can come up with a name that
 doesn't sound like an aggregate.  The best I can do offhand is
 number_of_nulls(), which doesn't seem very pretty.


 nulls_in(a, b, c) IN (0, 3) - yes the repetition is not great...


How about these:

nulls_rank() (the analogy being 0 = rank = set size)
nnulls()

or just

nulls() (this one might be a bit confusing due to existing NULLS LAST/FIRST
syntax, though)

--
Alex

Re: [HACKERS] count_nulls(VARIADIC any)

2015-08-12 Thread David G. Johnston

On Wed, Aug 12, 2015 at 4:11 PM, Tom Lane t...@sss.pgh.pa.us wrote:

 Peter Geoghegan p...@heroku.com writes:
  On Wed, Aug 12, 2015 at 10:30 AM, Alvaro Herrera
  alvhe...@2ndquadrant.com wrote:
  The name count_nulls() suggest an aggregate function to me, though.

  I thought the same.

 Ditto.  I'd be fine with this if we can come up with a name that
 doesn't sound like an aggregate.  The best I can do offhand is
 number_of_nulls(), which doesn't seem very pretty.


nulls_in(a, b, c) IN (0, 3) - yes the repetition is not great...

David J.

Re: [HACKERS] count_nulls(VARIADIC any)

2015-08-12 Thread Tom Lane

Peter Geoghegan p...@heroku.com writes:
 On Wed, Aug 12, 2015 at 10:30 AM, Alvaro Herrera
 alvhe...@2ndquadrant.com wrote:
 The name count_nulls() suggest an aggregate function to me, though.

 I thought the same.

Ditto.  I'd be fine with this if we can come up with a name that
doesn't sound like an aggregate.  The best I can do offhand is
number_of_nulls(), which doesn't seem very pretty.

regards, tom lane


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

[HACKERS] count_nulls(VARIADIC any)

2015-08-12 Thread Marko Tiikkaja


Hi,

I'd like to suggest $SUBJECT for inclusion in Postgres 9.6.  I'm sure 
everyone would've found it useful at some point in their lives, and the 
fact that it can't be properly implemented in any language other than C 
I think speaks for the fact that we as a project should provide it.


A quick and dirty proof of concept (patch attached):

=# select count_nulls(null::int, null::text, 17, 'bar');
 count_nulls
-
   2
(1 row)

Its natural habitat would be CHECK constraints, e.g:

  CHECK (count_nulls(a,b,c) IN (0, 3))

Will finish this up for the next CF, unless someone wants to tell me how 
stupid this idea is before that.



.m
*** a/src/backend/utils/adt/misc.c
--- b/src/backend/utils/adt/misc.c
***
*** 45,50 
--- 45,119 
  
  
  /*
+  * count_nulls()
+  *  Count the number of NULL input arguments
+  */
+ Datum
+ pg_count_nulls(PG_FUNCTION_ARGS)
+ {
+   int32 count = 0;
+   int i;
+ 
+   if (get_fn_expr_variadic(fcinfo-flinfo))
+   {
+   ArrayType  *arr;
+   int ndims, nitems, *dims;
+   bits8 *bitmap;
+   int bitmask;
+ 
+   /* Should have just the one argument */
+   Assert(PG_NARGS() == 1);
+ 
+   /* count_nulls(VARIADIC NULL) is defined as NULL */
+   if (PG_ARGISNULL(0))
+   PG_RETURN_NULL();
+ 
+   /*
+* Non-null argument had better be an array.  We assume that 
any call
+* context that could let get_fn_expr_variadic return true will 
have
+* checked that a VARIADIC-labeled parameter actually is an 
array.  So
+* it should be okay to just Assert that it's an array rather 
than
+* doing a full-fledged error check.
+*/
+   
Assert(OidIsValid(get_base_element_type(get_fn_expr_argtype(fcinfo-flinfo, 
0;
+ 
+   /* OK, safe to fetch the array value */
+   arr = PG_GETARG_ARRAYTYPE_P(0);
+ 
+   ndims = ARR_NDIM(arr);
+   dims = ARR_DIMS(arr);
+   nitems = ArrayGetNItems(ndims, dims);
+   //if (nitems == 0)
+   //  return PG_RETURN_INT32(0);
+ 
+   bitmap = ARR_NULLBITMAP(arr);
+   if (!bitmap)
+   PG_RETURN_INT32(0);
+ 
+   for (i = 0; i  nitems; i++)
+   {
+   if ((*bitmap  bitmask) == 0)
+   count++;
+ 
+   bitmask = 1;
+   if (bitmask == 0x100)
+   {
+   bitmap++;
+   bitmask = 1;
+   }
+   }
+   PG_RETURN_INT32(count);
+   }
+ 
+   for (i = 0; i  PG_NARGS(); i++)
+   {
+   if (PG_ARGISNULL(i))
+   count++;
+   }
+   PG_RETURN_INT32(count);
+ }
+ 
+ /*
   * current_database()
   *Expose the current database to the user
   */
*** a/src/include/catalog/pg_proc.h
--- b/src/include/catalog/pg_proc.h
***
*** 2959,2964  DESCR(adjust time with time zone precision);
--- 2959,2965 
  
  DATA(insert OID = 2003 (  textanycat PGNSP PGUID 14 1 0 0 0 f f f f t f s 
2 0 25 25 2776 _null_ _null_ _null_ _null_ _null_ select $1 || 
$2::pg_catalog.text _null_ _null_ _null_ ));
  DATA(insert OID = 2004 (  anytextcat PGNSP PGUID 14 1 0 0 0 f f f f t f s 
2 0 25 2776 25 _null_ _null_ _null_ _null_ _null_ select $1::pg_catalog.text 
|| $2 _null_ _null_ _null_ ));
+ DATA(insert OID = 3308 (  count_nullsPGNSP PGUID 12 1 0 2276 0 f f f f f 
f i 1 0 23 2276 {2276} {v} _null_ _null_ _null_pg_count_nulls _null_ 
_null_ _null_ ));
  
  DATA(insert OID = 2005 (  bytealike  PGNSP PGUID 12 1 0 0 0 f f f 
f t f i 2 0 16 17 17 _null_ _null_ _null_ _null_ _null_ bytealike _null_ 
_null_ _null_ ));
  DATA(insert OID = 2006 (  byteanlike PGNSP PGUID 12 1 0 0 0 f f f f t f i 
2 0 16 17 17 _null_ _null_ _null_ _null_ _null_ byteanlike _null_ _null_ 
_null_ ));
*** a/src/include/utils/builtins.h
--- b/src/include/utils/builtins.h
***
*** 481,486  extern Datum pg_ls_dir(PG_FUNCTION_ARGS);
--- 481,487 
  extern Datum pg_ls_dir_1arg(PG_FUNCTION_ARGS);
  
  /* misc.c */
+ extern Datum pg_count_nulls(PG_FUNCTION_ARGS);
  extern Datum current_database(PG_FUNCTION_ARGS);
  extern Datum current_query(PG_FUNCTION_ARGS);
  extern Datum pg_cancel_backend(PG_FUNCTION_ARGS);

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC any)

2015-08-12 Thread Greg Stark

On Wed, Aug 12, 2015 at 6:18 PM, Marko Tiikkaja ma...@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.

-- 
greg


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC any)

2015-08-12 Thread Alvaro Herrera

Greg Stark wrote:
 On Wed, Aug 12, 2015 at 6:18 PM, Marko Tiikkaja ma...@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.

I've seen cases where you want some entity to be of either of some
number of types.  In those cases you have nullable FKs in separate
columns, and only one of them can be non null.

The name count_nulls() suggest an aggregate function to me, though.

-- 
Álvaro Herrerahttp://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training  Services


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC any)

2015-08-12 Thread Pavel Stehule

Hi

2015-08-12 19:18 GMT+02:00 Marko Tiikkaja ma...@joh.to:

 Hi,

 I'd like to suggest $SUBJECT for inclusion in Postgres 9.6.  I'm sure
 everyone would've found it useful at some point in their lives, and the
 fact that it can't be properly implemented in any language other than C I
 think speaks for the fact that we as a project should provide it.

 A quick and dirty proof of concept (patch attached):

 =# select count_nulls(null::int, null::text, 17, 'bar');
  count_nulls
 -
2
 (1 row)

 Its natural habitat would be CHECK constraints, e.g:

   CHECK (count_nulls(a,b,c) IN (0, 3))

 Will finish this up for the next CF, unless someone wants to tell me how
 stupid this idea is before that.


 It is not bad idea

+1

Pavel



 .m


 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC any)

2015-08-12 Thread Marko Tiikkaja


On 2015-08-12 7:23 PM, Greg Stark wrote:

On Wed, Aug 12, 2015 at 6:18 PM, Marko Tiikkaja ma...@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


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC any)

2015-08-12 Thread Peter Geoghegan

On Wed, Aug 12, 2015 at 10:30 AM, Alvaro Herrera
alvhe...@2ndquadrant.com wrote:
 The name count_nulls() suggest an aggregate function to me, though.

I thought the same.


-- 
Peter Geoghegan


-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC any)

2015-08-12 Thread Marko Tiikkaja


On 2015-08-12 7:35 PM, Pavel Stehule wrote:

maybe nulls_count ?

we have regr_count already


But that's an aggregate as well..


.m


--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

Re: [HACKERS] count_nulls(VARIADIC any)

2015-08-12 Thread Pavel Stehule

2015-08-12 19:37 GMT+02:00 Marko Tiikkaja ma...@joh.to:

 On 2015-08-12 7:35 PM, Pavel Stehule wrote:

 maybe nulls_count ?

 we have regr_count already


 But that's an aggregate as well..


my mistake

Pavel




 .m

Re: [HACKERS] count_nulls(VARIADIC any)

2015-08-12 Thread Pavel Stehule

2015-08-12 19:32 GMT+02:00 Peter Geoghegan p...@heroku.com:

 On Wed, Aug 12, 2015 at 10:30 AM, Alvaro Herrera
 alvhe...@2ndquadrant.com wrote:
  The name count_nulls() suggest an aggregate function to me, though.

 I thought the same.


maybe nulls_count ?

we have regr_count already

Regards

Pavel




 --
 Peter Geoghegan


 --
 Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
 To make changes to your subscription:
 http://www.postgresql.org/mailpref/pgsql-hackers

47 matches

Mail list logo