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

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

Это ответ. Это ужасный анти-шаблон. Какой цели это служит? Если клиент знает таблицу, из которой он хочет получить данные, тогда 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". Эта абстракция просто сделает вашу систему хуже.

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

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;

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

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

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

Если вы хотите, чтобы имя таблицы, имя столбца и значение динамически передавались для функции в качестве параметра

используйте этот код

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

Если вопрос состоял в том, чтобы проверить, является ли таблица пустой или нет (id = 1), вот упрощенная версия Erwin, хранящая 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;

У меня есть версия 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