Определить имена таблиц и столбцов в качестве аргументов в функции 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 11
postgresql function plpgsql dynamic-sql

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);

В сторону: рассмотрите возможность использования just 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"и т.д. Следовательно, имена чувствительны к регистру в этом контексте.

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

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

26