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

Question

Это должно быть просто, но я делаю свои первые шаги в функциях 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;

Возможно ли это сделать?

Answer 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 спецификатор формата.

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

• INSERT с динамическим именем таблицы в функции триггера
• Как search_path влияет на разрешение идентификатора и "текущую схему"

В сторону: добавление нескольких столбцов с одним 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() чище...

Связанные ответы:

• Имя таблицы как параметр функции PostgreSQL
• Функции Postgres и подготовленные запросы

---

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

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

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

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