Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tbl1(ted1 int, ted2 int2, ted3 numeric);  -- numeric types
INSERT INTO tbl1 VALUES (1,1,1), (1,0,0), (1,0,0), (1,0,1);

CREATE TABLE tbl2(a_ted bool, b_ted bool, c_ted bool); -- bool types
INSERT INTO tbl2 VALUES (true,true,true), (true,false,false)
                      , (true,false,false), (true,false,true);
4 rows affected

4 rows affected

CREATE OR REPLACE FUNCTION f_tagcount(_tbl regclass, col_pattern text)
  RETURNS TABLE (tag text, tag_ct bigint)
  LANGUAGE plpgsql AS
$func$
BEGIN
   RETURN QUERY EXECUTE (
   SELECT
     'SELECT l.tag, count(l.val::int = 1 OR NULL)
      FROM ' || _tbl || ', LATERAL (VALUES '
       || string_agg( format('(%1$L, %1$I)', attname), ', ')
       || ') l(tag, val)
      GROUP  BY 1
      ORDER  BY 1'
   FROM   pg_catalog.pg_attribute
   WHERE  attrelid = _tbl
   AND    attname LIKE col_pattern
   AND    attnum > 0
   AND    NOT attisdropped
   );
END
$func$;
SELECT * FROM f_tagcount('tbl1', 'ted%');
SELECT * FROM f_tagcount('tbl2', '%ted');


tag
tag_ct


ted1
4

ted2
1

ted3
2




tag
tag_ct


a_ted
4

b_ted
1

c_ted
2