Имя таблицы как параметр функции PostgreSQL

Это можно еще больше упростить и улучшить:

CREATE OR REPLACE FUNCTION some_f(_tbl regclass, OUT result integer)
    LANGUAGE plpgsql AS
$func$
BEGIN
   EXECUTE format('SELECT (EXISTS (SELECT FROM %s WHERE id = 1))::int', _tbl)
   INTO result;
END
$func$;

Вызов с именем, указанным в схеме (см. Ниже):

SELECT some_f('myschema.mytable');  -- would fail with quote_ident()

Or:

SELECT some_f('"my very uncommon table name"');

Основные моменты

• Используйте OUT параметр, чтобы упростить функцию. Вы можете напрямую выбрать результат динамического SQL в нем и готово. Нет необходимости в дополнительных переменных и коде.

• EXISTS делает именно то, что вы хотите. Вы получите true, если строка существует, или false в противном случае. Есть разные способы сделать это, EXISTS обычно самый эффективный.

• Кажется, вам нужно вернуть целое число, поэтому я привел результат boolean с EXISTS на integer, что дает именно то, что было у вас. Вместо этого я бы вернул boolean.

• Я использую тип идентификатора объекта regclass как тип ввода для _tbl. Это все выполняет quote_ident(_tbl) или _ 15_ подойдет, но лучше, потому что:

• .. он также предотвращает инъекцию SQL.

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

• .. он работает с именами таблиц с указанием схемы, где простые quote_ident(_tbl) или format(%I) не работают, потому что они не могут разрешить двусмысленность. Вам придется передавать и экранировать имена схем и таблиц отдельно.

• Я все еще использую format() , потому что он упрощает синтаксис (и демонстрирует, как он используется), но с %s вместо %I. Обычно запросы более сложные, поэтому format() помогает больше. Для простого примера мы могли бы просто объединить:

    EXECUTE 'SELECT (EXISTS (SELECT FROM ' || _tbl || ' WHERE id = 1))::int'
  

• Нет необходимости уточнять столбец id, пока в списке FROM есть только одна таблица. В этом примере невозможна двусмысленность. (Динамические) команды SQL внутри EXECUTE имеют отдельную область действия, переменные функции или параметры там не отображаются - в отличие от простых команд SQL в теле функции.

db ‹› fiddle здесь, демонстрирующий внедрение SQL
< sub> Старый sqlfiddle

Если возможно, не делайте этого.

Это ответ - это антипаттерн. Если клиент знает таблицу, из которой ему нужны данные, то SELECT FROM ThatTable. Если база данных спроектирована таким образом, чтобы это требовалось, кажется, что она спроектирована неоптимально. Если уровень доступа к данным должен знать, существует ли значение в таблице, легко составить SQL в этом коде, и вставлять этот код в базу данных нецелесообразно.

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

Обратите внимание: здесь нет намерения насмехаться. Мой глупый пример с лифтом был * самым лучшим устройством, которое я мог представить * для лаконичного указания на проблемы с этой техникой. Он добавляет бесполезный уровень косвенного обращения, перемещая выбор имени таблицы из пространства вызывающего абонента (с использованием надежного и хорошо понятного DSL, SQL) в гибрид с использованием непонятного / причудливого кода SQL на стороне сервера.

Такое разделение ответственности за счет перемещения логики построения запроса в динамический SQL затрудняет понимание кода. Это нарушает стандартное и надежное соглашение (как SQL-запрос выбирает, что выбрать) в названии настраиваемого кода чревато ошибкой.

Вот подробные сведения о некоторых потенциальных проблемах, связанных с этим подходом:

• Динамический SQL предлагает возможность SQL-инъекции, которую трудно распознать в коде внешнего интерфейса или только в коде серверной части (чтобы увидеть это, нужно изучить их вместе).

• Сохраненные процедуры и функции могут получать доступ к ресурсам, на которые владелец SP / функции имеет права, а вызывающий - нет. Насколько я понимаю, без особой осторожности, то по умолчанию, когда вы используете код, который производит динамический SQL и запускает его, база данных выполняет динамический SQL под правами вызывающей стороны. Это означает, что вы либо вообще не сможете использовать привилегированные объекты, либо вам придется открыть их для всех клиентов, увеличивая поверхность потенциальной атаки на привилегированные данные. Установка SP / функции во время создания на постоянный запуск от имени конкретного пользователя (в SQL Server EXECUTE AS) может решить эту проблему, но усложняет задачу. Это усугубляет риск SQL-инъекции, упомянутый в предыдущем пункте, делая динамический SQL очень заманчивым вектором атаки.

• Когда разработчик должен понять, что делает код приложения, чтобы изменить его или исправить ошибку, ему будет очень трудно получить точный выполняемый SQL-запрос. Профилировщик SQL можно использовать, но это требует особых привилегий и может отрицательно сказаться на производительности производственных систем. Выполненный запрос может регистрироваться SP, но это увеличивает сложность с сомнительной выгодой (требует размещения новых таблиц, очистки старых данных и т. Д.) И совершенно неочевидно. Фактически, некоторые приложения спроектированы таким образом, что разработчик не имеет учетных данных базы данных, поэтому для него становится практически невозможным фактически увидеть отправляемый запрос.

• При возникновении ошибки, например, при попытке выбрать несуществующую таблицу, вы получите сообщение в строке «недопустимое имя объекта» из базы данных. Это произойдет точно так же, независимо от того, составляете ли вы SQL в серверной части или в базе данных, но разница в том, что какой-то плохой разработчик, пытающийся устранить неполадки в системе, должен углубиться на один уровень глубже в еще одну пещеру ниже той, где находится Проблема существует, чтобы покопаться в чудо-процедуре, которая делает все, чтобы попытаться выяснить, в чем проблема. В журналах не будет отображаться «Ошибка в GetWidget», будет отображаться «Ошибка в OneProcedureToRuleThemAllRunner». Эта абстракция обычно делает систему хуже.

Пример на псевдо-C # переключения имен таблиц на основе параметра:

string sql = $"SELECT * FROM {EscapeSqlIdentifier(tableName)};"
results = connection.Execute(sql);

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

Внутри кода plpgsql EXECUTE должен использоваться для запросов, в которых имена таблиц или столбцы берутся из переменных. Также конструкция IF EXISTS (<query>) не разрешена, когда query генерируется динамически.

CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer 
AS $$
DECLARE
 v int;
BEGIN
      EXECUTE 'select 1 FROM ' || quote_ident(param) || ' WHERE '
            || quote_ident(param) || '.id = 1' INTO v;
      IF v THEN return 1; ELSE return 0; END IF;
END;
$$ LANGUAGE plpgsql;

Первый на самом деле не «работает» в том смысле, в котором вы имеете в виду, он работает только постольку, поскольку он не вызывает ошибки.

Попробуйте SELECT * FROM quote_ident('table_that_does_not_exist');, и вы увидите, почему ваша функция возвращает 1: выборка возвращает таблицу с одним столбцом (с именем quote_ident) с одной строкой (переменная $1 или в данном конкретном случае table_that_does_not_exist).

То, что вы хотите сделать, потребует динамического SQL, который на самом деле является местом, где должны использоваться функции quote_*.

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

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

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

CREATE OR REPLACE FUNCTION some_f(_tbl varchar) returns integer
AS $$
BEGIN
    drop view if exists myview;
    execute format('create temporary view myview as select * from %s', _tbl);
    -- now you can reference myview in the SQL
    IF EXISTS (select * from myview where myview.id=1) THEN
     return 1;
    END IF;
    return 0;
END;
$$ language plpgsql;

Если вопрос заключался в том, чтобы проверить, пуста ли таблица или нет (id = 1), вот упрощенная версия хранимой процедуры Эрвина:

CREATE OR REPLACE FUNCTION isEmpty(tableName text, OUT zeroIfEmpty integer) AS
$func$
BEGIN
EXECUTE format('SELECT COALESCE ((SELECT 1 FROM %s LIMIT 1),0)', tableName)
INTO zeroIfEmpty;
END
$func$ LANGUAGE plpgsql;

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

используйте этот код

create or replace function total_rows(tbl_name text, column_name text, value int)
returns integer as $total$
declare
total integer;
begin
    EXECUTE format('select count(*) from %s WHERE %s = %s', tbl_name, column_name, value) INTO total;
    return total;
end;
$total$ language plpgsql;


postgres=# select total_rows('tbl_name','column_name',2); --2 is the value

У меня PostgreSQL версии 9.4, и я всегда использую этот код:

CREATE FUNCTION add_new_table(text) RETURNS void AS
$BODY$
begin
    execute
        'CREATE TABLE ' || $1 || '(
        item_1      type,
        item_2      type
        )';
end;
$BODY$
LANGUAGE plpgsql

А потом:

SELECT add_new_table('my_table_name');

У меня это работает хорошо.

Внимание! Приведенный выше пример является одним из тех, которые показывают «Как этого не сделать, если мы хотим сохранить безопасность при запросе к базе данных»: P