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

Я хочу передать имя таблицы в качестве параметра в функции Postgres. Я попробовал этот код:

CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer 
AS $$
    BEGIN
    IF EXISTS (select * from quote_ident($1) where quote_ident($1).id=1) THEN
     return 1;
    END IF;
    return 0;
    END;
$$ LANGUAGE plpgsql;

select some_f('table_name');

И я получил это:

ERROR:  syntax error at or near "."
LINE 4: ...elect * from quote_ident($1) where quote_ident($1).id=1)...
                                                             ^

********** Error **********

ERROR: syntax error at or near "."

И вот ошибка, которую я получил, когда изменился на этот select * from quote_ident($1) tab where tab.id=1:

ERROR:  column tab.id does not exist
LINE 1: ...T EXISTS (select * from quote_ident($1) tab where tab.id...

Наверное, quote_ident($1) работает, потому что без where quote_ident($1).id=1 часть я получаю 1, что означает, что что-то выбрано. Почему первый quote_ident($1) работа а вторая не одновременно? И как это можно решить?

Источник

7 ответов

Решение

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

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

Вызов (пример с именем, дополненным схемой - см. Ниже):

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

Или же:

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

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

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

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

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

  • Я использую тип идентификатора объекта regclass в качестве типа ввода для _tbl, Это все делает quote_ident(_tbl) или же format('%I', _tbl) будет делать, но лучше, потому что:

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

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

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

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

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

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

Протестировано с PostgreSQL 9.1. format() требует как минимум ту версию.

Вот почему вы всегда корректно избегаете пользовательского ввода для динамического SQL:

SQL Fiddle демонстрирует внедрение SQL

Источник

Не делай этого.

Это ответ. Это ужасный анти-паттерн. Какой цели это служит? Если клиент знает таблицу, из которой он хочет получить данные, то 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 = string.Format("SELECT * FROM {0};", 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;
Источник

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

Превращение всего сценария в динамический 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;
Источник

Первый на самом деле не "работает" в том смысле, что вы имеете в виду, он работает только постольку, поскольку он не генерирует ошибку.

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

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

Источник

Если вопрос состоял в том, чтобы проверить, является ли таблица пустой или нет (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

Источник
Другие вопросы по тегам