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


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

CREATE OR REPLACE FUNCTION some_f(param character varying) RETURNS integer 
AS $$
    BEGIN
    IF EXISTS (select * from quote_ident() where quote_ident().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() where quote_ident().id=1)...
                                                             ^

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

ERROR: syntax error at or near "."

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

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

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

7   53   2012-05-22 19:55:13

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, что дает именно то, что у вас было. Я бы вернулся boolean вместо.

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

    • .. это мешает SQL injection так же хорошо.

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

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

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

    EXECUTE 'SELECT (EXISTS (SELECT 1 FROM ' || _tbl || ' WHERE id = 1))::int'
    
  • нет необходимости в таблице-квалифицировать пока есть только одна таблица в FROM список. В этом примере нет никакой двусмысленности. (Динамические) команды SQL внутри EXECUTE есть отдельную область переменные функции или параметры там не видны - в отличие от простых команд SQL в теле функции.

протестировано с PostgreSQL 9.1. format() требует, по крайней мере, этой версии.

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

SQL Скрипка, демонстрирующая 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 profiler можно использовать, но это требует особых привилегий и может негативно сказаться на производительности производственных систем. Выполненный запрос может быть зарегистрирован SP, но это увеличивает сложность без причины (ведение новых таблиц, очистка старых данных и т. д.) и совершенно неочевидно. На самом деле, некоторые приложения имеют такую архитектуру, что разработчик не имеет учетных данных базы данных, поэтому для него становится практически невозможным фактически увидеть отправляемый запрос.

  • при возникновении ошибки, например при попытке выбрать таблицу, которая не существует, вы получите сообщение примерно следующего содержания: "недопустимое имя объекта" из базы данных. Именно это и произойдет то же самое, составляете ли вы SQL в задней части или базе данных, но разница в том, что какой-то бедный разработчик, который пытается устранить неполадки в системе, должен погрузиться на один уровень глубже в еще одну пещеру ниже той, где проблема действительно существует, чтобы копаться в чудо-процедуре, которая все это делает, и попытаться выяснить, в чем проблема. Журналы не будут показывать "ошибка в GetWidget", он покажет "ошибка в OneProcedureToRuleThemAllRunner". Эта абстракция просто сделает вашу систему хуже.

вот гораздо лучший пример в псевдо-C# переключения имен таблиц на основе параметра:

string sql = string.Format("SELECT * FROM {0};", escapeSqlIdentifier(tableName));
results = connection.Execute(sql);

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

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

внутри кода plpgsql,выполнить оператор должен использоваться для запросов, в которых имена таблиц или столбцов поступают из переменных. Кроме того,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: select возвращает таблицу с одним столбцом (с именем quote_ident) С одной строкой (переменная или в данном конкретном случае table_that_does_not_exist).

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

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

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

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

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

и затем:

SELECT add_new_table('my_table_name');

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

внимание! выше пример является одним из тех, которые показывают "как не делать, если мы хотим сохранить безопасность во время запроса к базе данных" :P