public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v3] popcount
2+ messages / 2 participants
[nested] [flat]
* [PATCH v3] popcount
@ 2020-12-30 10:51 David Fetter <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: David Fetter @ 2020-12-30 10:51 UTC (permalink / raw)
Now it's accessible to SQL for the BIT VARYING and BYTEA types.
diff --git doc/src/sgml/func.sgml doc/src/sgml/func.sgml
index 02a37658ad..1d86d610dd 100644
--- doc/src/sgml/func.sgml
+++ doc/src/sgml/func.sgml
@@ -4069,6 +4069,23 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
</para></entry>
</row>
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>popcount</primary>
+ </indexterm>
+ <function>popcount</function> ( <parameter>bytes</parameter> <type>bytea</type> )
+ <returnvalue>bigint</returnvalue>
+ </para>
+ <para>
+ Counts the number of bits set in a binary string.
+ </para>
+ <para>
+ <literal>popcount('\xdeadbeef'::bytea)</literal>
+ <returnvalue>24</returnvalue>
+ </para></entry>
+ </row>
+
<row>
<entry role="func_table_entry"><para role="func_signature">
<indexterm>
@@ -4830,6 +4847,24 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<returnvalue>101010001010101010</returnvalue>
</para></entry>
</row>
+
+ <row>
+ <entry role="func_table_entry"><para role="func_signature">
+ <indexterm>
+ <primary>popcount</primary>
+ </indexterm>
+ <function>popcount</function> ( <parameter>bits</parameter> <type>bit</type> )
+ <returnvalue>bigint</returnvalue>
+ </para>
+ <para>
+ Counts the bits set in a bit string.
+ </para>
+ <para>
+ <literal>popcount(B'101010101010101010')</literal>
+ <returnvalue>9</returnvalue>
+ </para></entry>
+ </row>
+
</tbody>
</tgroup>
</table>
diff --git src/include/catalog/pg_proc.dat src/include/catalog/pg_proc.dat
index d7b55f57ea..2d53e0d46d 100644
--- src/include/catalog/pg_proc.dat
+++ src/include/catalog/pg_proc.dat
@@ -1446,6 +1446,9 @@
{ oid => '752', descr => 'substitute portion of string',
proname => 'overlay', prorettype => 'bytea',
proargtypes => 'bytea bytea int4', prosrc => 'byteaoverlay_no_len' },
+{ oid => '8436', descr => 'count set bits',
+ proname => 'popcount', prorettype => 'int8', proargtypes => 'bytea',
+ prosrc => 'byteapopcount'},
{ oid => '725',
proname => 'dist_pl', prorettype => 'float8', proargtypes => 'point line',
@@ -3865,6 +3868,9 @@
{ oid => '3033', descr => 'set bit',
proname => 'set_bit', prorettype => 'bit', proargtypes => 'bit int4 int4',
prosrc => 'bitsetbit' },
+{ oid => '8435', descr => 'count set bits',
+ proname => 'popcount', prorettype => 'int8', proargtypes => 'bit',
+ prosrc => 'bitpopcount'},
# for macaddr type support
{ oid => '436', descr => 'I/O',
diff --git src/backend/utils/adt/varbit.c src/backend/utils/adt/varbit.c
index 2235866244..a6a44f3f4f 100644
--- src/backend/utils/adt/varbit.c
+++ src/backend/utils/adt/varbit.c
@@ -36,6 +36,7 @@
#include "libpq/pqformat.h"
#include "nodes/nodeFuncs.h"
#include "nodes/supportnodes.h"
+#include "port/pg_bitutils.h"
#include "utils/array.h"
#include "utils/builtins.h"
#include "utils/varbit.h"
@@ -1878,3 +1879,29 @@ bitgetbit(PG_FUNCTION_ARGS)
else
PG_RETURN_INT32(0);
}
+
+/*
+ * bitpopcount
+ *
+ * Returns the number of bits set in a bit array.
+ *
+ */
+Datum
+bitpopcount(PG_FUNCTION_ARGS)
+{
+ VarBit *arg1 = PG_GETARG_VARBIT_P(0);
+ bits8 *p;
+ int len;
+ int8 popcount;
+
+ /*
+ * ceil(VARBITLEN(ARG1)/BITS_PER_BYTE)
+ * done to minimize branches and instructions.
+ */
+ len = (VARBITLEN(arg1) + BITS_PER_BYTE + 1) / BITS_PER_BYTE;
+ p = VARBITS(arg1);
+
+ popcount = pg_popcount((const char *)p, len);
+
+ PG_RETURN_INT64(popcount);
+}
diff --git src/backend/utils/adt/varlena.c src/backend/utils/adt/varlena.c
index 4838bfb4ff..da3ba769c4 100644
--- src/backend/utils/adt/varlena.c
+++ src/backend/utils/adt/varlena.c
@@ -3433,6 +3433,22 @@ bytea_overlay(bytea *t1, bytea *t2, int sp, int sl)
return result;
}
+/*
+ * popcount
+ */
+Datum
+byteapopcount(PG_FUNCTION_ARGS)
+{
+ bytea *t1 = PG_GETARG_BYTEA_PP(0);
+ int len;
+ int8 result;
+
+ len = VARSIZE_ANY_EXHDR(t1);
+ result = pg_popcount(VARDATA_ANY(t1), len);
+
+ PG_RETURN_INT64(result);
+}
+
/*
* byteapos -
* Return the position of the specified substring.
diff --git src/test/regress/expected/bit.out src/test/regress/expected/bit.out
index a7f95b846d..228f992ced 100644
--- src/test/regress/expected/bit.out
+++ src/test/regress/expected/bit.out
@@ -710,6 +710,19 @@ SELECT overlay(B'0101011100' placing '001' from 20);
0101011100001
(1 row)
+-- Popcount
+SELECT popcount(B'0101011100'::bit(10));
+ popcount
+----------
+ 5
+(1 row)
+
+SELECT popcount(B'1111111111'::bit(10));
+ popcount
+----------
+ 10
+(1 row)
+
-- This table is intentionally left around to exercise pg_dump/pg_upgrade
CREATE TABLE bit_defaults(
b1 bit(4) DEFAULT '1001',
diff --git src/test/regress/expected/strings.out src/test/regress/expected/strings.out
index 595bd2446e..dd962e5f48 100644
--- src/test/regress/expected/strings.out
+++ src/test/regress/expected/strings.out
@@ -2167,3 +2167,10 @@ SELECT encode(overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 5
Th\000o\x02\x03
(1 row)
+SET bytea_output TO hex;
+SELECT popcount(E'\\xdeadbeef'::bytea);
+ popcount
+----------
+ 24
+(1 row)
+
diff --git src/test/regress/sql/bit.sql src/test/regress/sql/bit.sql
index ea01742c4a..fa77bf45c4 100644
--- src/test/regress/sql/bit.sql
+++ src/test/regress/sql/bit.sql
@@ -215,6 +215,10 @@ SELECT overlay(B'0101011100' placing '101' from 6);
SELECT overlay(B'0101011100' placing '001' from 11);
SELECT overlay(B'0101011100' placing '001' from 20);
+-- Popcount
+SELECT popcount(B'0101011100'::bit(10));
+SELECT popcount(B'1111111111'::bit(10));
+
-- This table is intentionally left around to exercise pg_dump/pg_upgrade
CREATE TABLE bit_defaults(
b1 bit(4) DEFAULT '1001',
diff --git src/test/regress/sql/strings.sql src/test/regress/sql/strings.sql
index ca465d050f..48964e71cb 100644
--- src/test/regress/sql/strings.sql
+++ src/test/regress/sql/strings.sql
@@ -728,3 +728,6 @@ SELECT btrim(E'\\000trim\\000'::bytea, ''::bytea);
SELECT encode(overlay(E'Th\\000omas'::bytea placing E'Th\\001omas'::bytea from 2),'escape');
SELECT encode(overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 8),'escape');
SELECT encode(overlay(E'Th\\000omas'::bytea placing E'\\002\\003'::bytea from 5 for 3),'escape');
+
+SET bytea_output TO hex;
+SELECT popcount(E'\\xdeadbeef'::bytea);
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options
@ 2025-02-03 11:45 Tatsuo Ishii <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Tatsuo Ishii @ 2025-02-03 11:45 UTC (permalink / raw)
To: [email protected]; +Cc: [email protected]; [email protected]; [email protected]; pgsql-hackers; [email protected]; [email protected]
> I've looked at it again and I think the code is correct, but I
> miswrote that the array needs to be sorted. The above query returns:
> x | y | nth_value
> ---+---+-----------
> 1 | 1 | 2
> 2 | 2 | 1
> 3 | | 2
> 4 | 4 |
> 5 | | 4
> 6 | 6 | 7
> 7 | 7 | 6
> (7 rows)
>
> This is correct, for values of x:
>
> 1: The first non-null value of y is at position 0, however we have
> EXCLUDE CURRENT ROW so it picks the next non-null value at position 1
> and stores it in the array, returning 2.
> 2: We can now take the first non-null value of y at position 0 and
> store it in the array, returning 1.
> 3. We take 1 preceding, using the position stored in the array, returning 2.
> 4. 1 preceding and 1 following are both null, and we exclude the
> current row, so returning null.
> 5. 1 preceding is at position 3, store it in the array, returning 4.
> 6. 1 preceding is null and we exclude the current row, so store
> position 6 in the array, returning 7.
> 7. 1 preceding is at position 5, store it in the array and return 6.
>
> It will be unordered when the EXCLUDE clause is used but the code
> should handle this correctly.
I ran this query (not using IGNORE NULLS) and get a result.
SELECT
x,
nth_value(x,2) OVER w
FROM generate_series(1,5) g(x)
WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW);
x | nth_value
---+-----------
1 | 3
2 | 3
3 | 2
4 | 3
5 | 4
(5 rows)
Since there's no NULL in x column, I expected the same result using
IGNORE NULLS, but it was not:
SELECT
x,
nth_value(x,2) IGNORE NULLS OVER w
FROM generate_series(1,5) g(x)
WINDOW w AS (ORDER BY x ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING EXCLUDE CURRENT ROW);
x | nth_value
---+-----------
1 | 3
2 | 4
3 | 4
4 | 3
5 | 4
(5 rows)
I suspect the difference is in the code path of
ignorenulls_getfuncarginframe and the code path in
WinGetFuncArgInFrame, which takes care of EXCLUDE like this.
case FRAMEOPTION_EXCLUDE_CURRENT_ROW:
if (abs_pos >= winstate->currentpos &&
winstate->currentpos >= winstate->frameheadpos)
abs_pos++;
Best reagards,
--
Tatsuo Ishii
SRA OSS K.K.
English: http://www.sraoss.co.jp/index_en/
Japanese:http://www.sraoss.co.jp
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2025-02-03 11:45 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-12-30 10:51 [PATCH v3] popcount David Fetter <[email protected]>
2025-02-03 11:45 Re: Add RESPECT/IGNORE NULLS and FROM FIRST/LAST options Tatsuo Ishii <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox