Динамическое выполнение запросов в PL/pgSQL

Question

Я нашел решение (я думаю) проблемы, которую я собираюсь попросить на Oracle и SQL Server, но, похоже, не могу перевести это в решение Postgres. Я использую Postgres 9.3.6.

Идея состоит в том, чтобы иметь возможность генерировать "метаданные" о содержимом таблицы для целей профилирования. Это можно сделать только (AFAIK), запустив запросы для каждого столбца, чтобы выяснить, скажем... значения min/max/count и тому подобное. Чтобы автоматизировать процедуру, предпочтительно, чтобы запросы, сгенерированные БД, затем выполнялись.

С примером salesdata Таблица, я могу сгенерировать запрос на выборку для каждого столбца, возвращая значение min(), используя следующий фрагмент:

SELECT 'SELECT min('||column_name||') as minval_'||column_name||' from salesdata '  
FROM information_schema.columns 
WHERE table_name = 'salesdata'

Преимущество в том, что БД будет генерировать код независимо от количества столбцов. Теперь я имел в виду множество мест для хранения этих запросов, либо какой-то переменной, либо столбца таблицы, идея состояла в том, чтобы затем эти запросы выполнялись. Я думал о том, чтобы сохранить сгенерированные запросы в переменной, а затем выполнить их с помощью EXECUTE (или же EXECUTE IMMEDIATE), который является подходом, используемым здесь (см. правую панель), но Postgres не позволит мне объявить переменную вне функции, и я ломал голову над тем, как это будет сочетаться, будь то направление, которому нужно следовать, возможно есть что-то попроще

Будете ли вы иметь какие-либо указатели, я в настоящее время пытаюсь что-то вроде этого, вдохновленный этим другим вопросом, но не знаю, направляюсь ли я в правильном направлении:

CREATE OR REPLACE FUNCTION foo()
RETURNS void AS
$$
DECLARE
    dyn_sql text; 
BEGIN            
dyn_sql := SELECT 'SELECT min('||column_name||') from salesdata'    
    FROM information_schema.columns 
    WHERE table_name = 'salesdata';
execute dyn_sql
END
$$ LANGUAGE PLPGSQL;    

Answer 1

Системная статистика

Перед тем как катиться самостоятельно, взгляните на системный стол pg_statistic или вид pg_stats:

Это представление разрешает доступ только к тем строкам pg_statistic, которые соответствуют таблицам, на которые пользователь имеет разрешение на чтение, и поэтому безопасно разрешить публичный доступ для чтения к этому представлению.

Возможно, он уже содержит некоторые статистические данные, которые вы собираетесь вычислить. Это населено ANALYZE так что вы можете запустить это для новых (или любых) таблиц перед проверкой.

-- ANALYZE tbl;  -- optionally, to init / refresh
SELECT * FROM pg_stats
WHERE tablename = 'tbl'
AND   schemaname = 'public';

Общая динамическая функция plpgsql

Похоже, вы хотите вернуть минимальное значение для каждого столбца в данной таблице. Это не тривиальная задача, потому что функция (например, SQL в целом) требует знать тип возвращаемого значения во время создания или, по крайней мере, во время вызова с помощью полиморфных типов данных.

Эта функция делает все автоматически и безопасно. Работает для любой таблицы, пока агрегатная функция min() разрешено для каждого столбца. Но вы должны знать, как обходить plpgsql.

CREATE OR REPLACE FUNCTION f_min_of(_tbl anyelement)
  RETURNS SETOF anyelement AS
$func$
BEGIN
RETURN QUERY EXECUTE (
   SELECT format('SELECT (t::%2$s).* FROM (SELECT min(%1$s) FROM %2$s) t'
                , string_agg(quote_ident(attname), '), min(' ORDER BY attnum)
                , pg_typeof(_tbl)::text)
   FROM   pg_attribute
   WHERE  attrelid = pg_typeof(_tbl)::text::regclass
   AND    NOT attisdropped  -- no dropped (dead) columns
   AND    attnum > 0        -- no system columns
   );
END
$func$  LANGUAGE plpgsql;

Вызов:

SELECT * FROM f_min_of(NULL::tbl);  -- tbl being the table name

SQL Fiddle.

Вы должны понимать эти понятия:

• Динамический SQL в plpgsql с EXECUTE,
• Полиморфные типы.
• Типы строк и типы таблиц в Postgres.
• Как защититься от внедрения SQL.
• Агрегатные функции.
• Системные каталоги.

Соответствующий ответ с подробным объяснением:

• Имя таблицы как параметр функции PostgreSQL
• Рефакторинг функции PL/pgSQL для возврата результатов различных запросов SELECT
• Тип данных Postgres
• Как установить значение поля составной переменной с помощью динамического SQL
• Как проверить, существует ли таблица в данной схеме
• Выберите столбцы с определенными именами столбцов в PostgreSQL
• Создать серию дат - используя тип даты в качестве входных данных

Особая сложность с несоответствием типов

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

Однако некоторые агрегатные функции возвращают связанные, но разные типы данных по сравнению с базовым столбцом. Например, min(varchar_column) возвращается text, который является бит-совместимым, но не совсем того же типа данных. У функций plpgsql есть слабое место, и они настаивают на типах данных точно так, как объявлено в RETURNS пункт. Никаких попыток бросать, даже неявных приведений, не говоря уже о приведениях назначений.

Это должно быть улучшено. Протестировано с Postgres 9.3. Не тестировал с 9.4, но я уверен, что ничего не изменилось в этой области.

Вот где эта конструкция входит:

SELECT (t::tbl).* FROM (SELECT ... FROM tbl) t;

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

Имейте в виду, что это может не сработать для некоторой агрегатной функции. sum() возвращается numeric для sum(bigint_column) учитывать сумму, превышающую базовый тип данных. Возвращение к bigint может потерпеть неудачу...

Answer 2

@Erwin Brandstetter, Большое спасибо за исчерпывающий ответ. pg_stats действительно предоставляет несколько вещей, но мне действительно нужно нарисовать полный профиль, это множество вещей, минимальных, максимальных значений, количества, количества нулей, среднего значения и т. д., поэтому необходимо выполнить несколько запросов для каждый столбец, некоторые с GROUP BY и такие.

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

Я попробовал функцию, которую вы предоставляете (мне, вероятно, нужно будет начать изучать некоторые plpgsql), но получить ошибку в SELECT (t::tbl):

ERROR: type "tbl" does not exist

Кстати, как обозначается обозначение (t::abc), в python это будет фрагмент списка, но, вероятно, это не так в PLPGSQL