Postgres 15

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE SCHEMA datastore;
CREATE TABLE datastore.t1 (dss_current_flag text);
CREATE TABLE datastore.t2 (dss_current_flag text);

INSERT INTO datastore.t1 VALUES ('foo'),('bar');
INSERT INTO datastore.t2 VALUES ('foo');

CREATE SCHEMA

CREATE TABLE

CREATE TABLE

INSERT 0 2

INSERT 0 1

CREATE OR REPLACE FUNCTION count_records() 
RETURNS bigint AS $$
DECLARE  
  rec record;
  res bigint = 0; ct bigint = 0;
BEGIN
  FOR rec IN 
    SELECT table_schema AS sch,table_name AS tb
    FROM information_schema.columns
    WHERE table_schema = 'datastore' AND column_name = 'dss_current_flag' 	
  LOOP
    EXECUTE format($ex$ SELECT count(*) FROM %I.%I $ex$,rec.sch,rec.tb) 
	INTO ct;
	res := res + ct;
  END LOOP;
  RETURN res;
END $$ LANGUAGE 'plpgsql';

SELECT count_records(); 

CREATE FUNCTION

count_records

3

SELECT 1

CREATE OR REPLACE FUNCTION count_records2(_schema_name text, _table_name text) 
RETURNS bigint AS $$
DECLARE  
  rec record;
  res bigint = 0; ct bigint = 0;
BEGIN
  FOR rec IN 
    SELECT table_schema AS sch,table_name AS tb
    FROM information_schema.columns
    WHERE table_schema = $1 AND column_name = $2 	
  LOOP
    EXECUTE format($ex$ SELECT count(*) FROM %I.%I $ex$,rec.sch,rec.tb) 
	INTO ct;
	res := res + ct;
  END LOOP;
  RETURN res;
END $$ LANGUAGE 'plpgsql';

SELECT count_records2('datastore','dss_current_flag');  

CREATE FUNCTION

count_records2

3

SELECT 1

CREATE OR REPLACE FUNCTION count_records3(_qualified_table_name text) 
RETURNS bigint AS $$
DECLARE  
  rec record;
  res bigint = 0; ct bigint = 0;
BEGIN
  FOR rec IN 
    SELECT table_schema AS sch,table_name AS tb
    FROM information_schema.columns
    WHERE table_schema ||'.'||column_name = $1 	
  LOOP
    EXECUTE format($ex$ SELECT count(*) FROM %I.%I $ex$,rec.sch,rec.tb) 
	INTO ct;
	res := res + ct;
  END LOOP;
  RETURN res;
END $$ LANGUAGE 'plpgsql';

SELECT count_records3('datastore.dss_current_flag');  

CREATE FUNCTION

count_records3

3

SELECT 1