Use lateral join to loop over all tables from schema

Question

I want to count certain values in all tables of a schema that contain a column that can contain those values.

Was hoping to use a LATERAL join to loop over all tables, but I'm running into issues:

select
    fully_qualified_table_name,
    cnt
from (
    select
        'datastore.' || table_name as fully_qualified_table_name
    from
        information_schema.columns
    where
        table_schema = 'datastore'
        and column_name = 'dss_current_flag'
    
    cross join lateral
    
    select
        count(*) as cnt
    from
        information_schema.fully_qualified_table_name
    );

Is this possible?

Answer 1

Based on the answer by @jim-jones my final solution was

CREATE TYPE datastore.schema_table_column_counts_type AS (
    schema_name text,
    table_name text,
    column_name text,
    value text,
    count_p bigint);

CREATE OR REPLACE FUNCTION datastore.count_records_in_schema_where_column_has_value(_schema_name text, _column_name text, _value text) 
RETURNS setof datastore.schema_table_column_counts_type language plpgsql AS $$
DECLARE  
  rec record;
  result_record datastore.schema_table_column_counts_type;
BEGIN   
  FOR rec IN 
    SELECT 
        table_schema AS sch,
        table_name AS tb, 
        $2 as cn, 
        $3 as v
    FROM information_schema.columns
    WHERE table_schema = $1
    AND column_name = $2
  LOOP
    EXECUTE format($ex$ 
        SELECT 
            '%1$s' as schema_name, 
            '%2$s' as table_name, 
            '%3$s' as column_name,
            '%4$s' as value,
            count(*) 
        FROM 
            %1$s.%2$s
        WHERE
            %3$s = %4$L 
        $ex$
        , rec.sch, rec.tb, rec.cn, rec.v) 
    INTO result_record;
    return next result_record;
  END LOOP;
END $$ ;

SELECT * from datastore.count_records_in_schema_where_column_has_value('datastore', 'dss_current_flag', 'P');