Определить имена таблиц и столбцов в качестве аргументов в функции plpgsql?

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

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

Что-то вроде:

CREATE OR REPLACE FUNCTION foo(t table)
RETURNS void AS $$
BEGIN
   alter table t add column c1 varchar(20);
   alter table t add column c2 varchar(20);
   alter table t add column c3 varchar(20);
   alter table t add column c4 varchar(20);
END;
$$ LANGUAGE PLPGSQL;

select foo(some_table)

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

CREATE OR REPLACE FUNCTION foo(t table, c column)
RETURNS void AS $$
BEGIN
   UPDATE t SET c = "This is a test";
END;
$$ LANGUAGE PLPGSQL;

Можно ли это сделать?

Ответы

Ответ 1

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

1. format()

1-й запрос, дезинфицированный:

CREATE OR REPLACE FUNCTION foo(_t text)
  RETURNS void AS
$func$
BEGIN
   EXECUTE format('
   ALTER TABLE %I ADD COLUMN c1 varchar(20)
                , ADD COLUMN c2 varchar(20)', _t);
END
$func$  LANGUAGE plpgsql;

format() требуется Postgres 9.1 или новее. Используйте его с помощью спецификатора формата %I.

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

Помимо этого: добавление нескольких столбцов с одной командой ALTER TABLE дешевле.

2. regclass

Вы также можете использовать приведение к зарегистрированному классу (regclass) для специального случая имен существующих. Необязательно с учетом схемы. Это немедленно и изящно не работает для имен таблиц, которые не являются действительными и видимыми вызывающему пользователю. 1-й запрос, обработанный приложением regclass:

CREATE OR REPLACE FUNCTION foo(_t regclass)
  RETURNS void AS
$func$
BEGIN
   EXECUTE 'ALTER TABLE '|| _t ||' ADD COLUMN c1 varchar(20)
                                 , ADD COLUMN c2 varchar(20)';
END
$func$  LANGUAGE plpgsql;

Вызов:

SELECT foo('table_name');

Или:

SELECT foo('my_schema.table_name'::regclass);

Кроме того: рассмотрите возможность использования text вместо varchar(20).

3. quote_ident()

2-й запрос, дезинфицированный:

CREATE OR REPLACE FUNCTION foo(_t regclass, _c text)
  RETURNS void AS
$func$
BEGIN
   EXECUTE 'UPDATE '|| _t ||'   -- sanitized with regclass
            SET '|| quote_ident(_c) ||' = ''This is a test''';
END
$func$  LANGUAGE plpgsql;

Для нескольких конкатенаций/интерполяций format() является более чистым...

Похожие ответы:

С учетом регистра!

Имейте в виду, что некотируемые идентификаторы не отображаются в нижнем регистре. При использовании в качестве идентификатора в SQL Postgres автоматически переходит к нижнему регистру. Но здесь мы передаем строки для динамического SQL. При экранировании, как показано, идентификаторы CaMel-case (например, UserS) будут сохранены путем двойного запуска ("UserS"), как и другие нестандартные имена, такие как "name with space" "SELECT" и т.д. Следовательно, имена в этом контексте чувствительны к регистру.

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

Помимо: одинарные кавычки для значений, двойные кавычки для идентификаторов.