Используйте боковое соединение, чтобы перебрать все таблицы из схемы

Я хочу подсчитать определенные значения во всех таблицах схемы, содержащих столбец, который может содержать эти значения.

Надеялся использовать соединение LATERAL для перебора всех таблиц, но столкнулся с проблемами:

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
    );

Это возможно?

stackoverflow.com/questions/2596670/…

— 10.01.2023 11:37

sql postgresql plpgsql

10.01.2023 10:44

ReactJs | Supabase | Добавление данных в базу данных

Это и есть ваш редактор таблиц в supabase.👇

Понимание Python и переход к SQL

Перед нами лабораторная работа по BloodOath:

Перейти к ответу Данный вопрос помечен как решенный

Ответы 2

Я боюсь, что невозможно выполнять динамические запросы, используя чистый SQL. Вместо этого вы можете проверить PL/pgSQL, например.

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';

Более гибким подходом было бы предоставление имен схем и таблиц в качестве параметров вызова функции вместо жесткого кодирования их в теле функции, например CREATE FUNCTION count_records(_schema_name text, _table_name text) .., или даже полного имени таблицы в качестве одного параметра: CREATE FUNCTION count_records(_qualified_table_name text) ... .

Демо: db<>рабочий пример

10.01.2023 11:21

Ответ принят как подходящий

Основываясь на ответе @jim-jones, мое окончательное решение было

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');

выглядит неплохо! +1 просто будьте осторожны с %1$s.%2$s, если у вас есть таблицы с именами, содержащими специальные символы .. ура!

— 13.01.2023 12:27

13.01.2023 11:17