Передача имен таблиц в массиве

Мне нужно сделать одну и ту же операцию удаления или очистки (на основе нескольких условий) для набора таблиц. Для этого я пытаюсь передать имена таблиц в массиве функции. Я не уверен, правильно ли я это делаю. Или есть лучший способ?

Я вставляю только пример, это не настоящая функция, которую я написал, но основная такая же, как показано ниже:

CREATE OR REPLACE FUNCTION test (tablename text[]) RETURNS int AS
$func$
BEGIN

execute 'delete  * from  '||tablename;
  RETURN 1;
END
$func$ LANGUAGE plpgsql;

Но когда я вызываю функцию, я получаю сообщение об ошибке:

select test( {'rajeev1'} );

ERROR:  syntax error at or near "{"
LINE 10: select test( {'rajeev1'} );
                      ^
********** Error **********

ERROR: syntax error at or near "{"
SQL state: 42601
Character: 179
Источник

3 ответа

Решение

Вы использовали неправильный синтаксис для text array constant в вызове функции. Но даже если это было правильно, ваша функция не является правильной.

Если ваша функция имеет text array в качестве аргумента вы должны перебрать массив для выполнения запроса для каждого элемента.

CREATE OR REPLACE FUNCTION test (tablenames text[]) RETURNS int AS
$func$
DECLARE
    tablename text;
BEGIN
    FOREACH tablename IN ARRAY tablenames LOOP
        EXECUTE FORMAT('delete  * from  %s', tablename);
    END LOOP;
    RETURN 1;
END
$func$ LANGUAGE plpgsql;

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

SELECT test( '{rajeev1, rajeev2}' );

Если вам не нужна эта функция, просто измените тип аргумента на text,

CREATE OR REPLACE FUNCTION test (tablename text) RETURNS int AS
$func$
BEGIN
    EXECUTE format('delete  * from  %s', tablename);
    RETURN 1;
END
$func$ LANGUAGE plpgsql;

SELECT test('rajeev1');

Я рекомендую использовать функцию форматирования.

Если вы хотите выполнить функцию (скажем, purge_this_one_table(tablename)) в группе таблиц, идентифицированных похожими именами, вы можете использовать эту конструкцию:

create or replace function purge_all_these_tables(mask text)
returns void language plpgsql 
as $$
declare
    tabname text;
begin
    for tabname in
        select relname 
        from pg_class
        where relkind = 'r' and relname like mask
    loop
        execute format(
            'purge_this_one_table(%s)',
            tabname);
    end loop;
end $$;

select purge_all_these_tables('agg_weekly_%');
Источник

Синтаксис массива

'{rajeev1, rajeev2}' или же ARRAY['rajeev1', 'rajeev2'], Прочтите руководство.

TRUNCATE

Поскольку вы удаляете все строки из таблиц, рассмотрите TRUNCATE вместо. По документации:

Совет: TRUNCATE это расширение PostgreSQL, которое обеспечивает более быстрый механизм удаления всех строк из таблицы.

Обязательно изучите детали. Если TRUNCATE работает для вас, вся операция становится очень простой, так как команда принимает несколько таблиц:

TRUNCATE rajeev1, rajeev2, rajeev3, ..

динамический DELETE

Иначе вам нужен динамический SQL, как вы уже пробовали. Страшная недостающая деталь: вы полностью открыты для SQL-инъекций и катастрофических синтаксических ошибок. использование format() с %I (не %s дезинфицировать идентификаторы, такие как имена таблиц. Или, что еще лучше в данном конкретном случае, использовать массив regclass вместо параметра:

CREATE OR REPLACE FUNCTION f_del_all(_tbls regclass)
  RETURNS void AS
$func$
DECLARE
   _tbl regclass;
BEGIN
   FOREACH _tbl IN ARRAY _tbls LOOP
      EXECUTE format('DELETE * FROM %s', _tbl);
   END LOOP;
END
$func$ LANGUAGE plpgsql;

Вызов:

SELECT f_del_all('{rajeev1,rajeev2,rajeev3}');

Объяснение здесь:
Имя таблицы как параметр функции PostgreSQL

Источник

Так должно быть:

select test('{rajeev1}');
Источник
Другие вопросы по тегам