在数组中传递表名 [英] Passing table names in an array

问题描述

我需要对一组表执行相同的删除或清除操作（基于多种条件）。为此，我试图将数组中的表名传递给函数。我不确定我是否做对了。还是有更好的方法？

I need to do the same deletion or purge operation (based on several conditions) on a set of tables. For that I am trying to pass the table names in an array to a function. I am not sure if I am doing it right. Or is there a better way?

我仅粘贴了一个示例示例，这不是我编写的真实函数，但基本内容与下面相同：

I am pasting just a sample example this is not the real function I have written but the basic is same as below:

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

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

但是当我调用该函数时会出现错误：

But when I call the function I get an error:

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

推荐答案

您在函数调用中为文本数组常量使用了错误的语法。但是，即使正确，函数也不正确。

You used wrong syntax for text array constant in the function call. But even if it was right, your function is not correct.

如果函数具有文本数组作为参数，应该遍历数组以对每个元素执行查询。

If your function has text array as argument you should loop over the array to execute query for each element.

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;

然后您可以一次调用多个表的函数，而不仅仅是调用一个表。

You can then call the function for several tables at once, not only for one.

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

如果不需要此功能，只需将参数类型更改为 text 。

If you do not need this feature, simply change the argument type to 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');

我建议使用 格式函数。

I recommend using the format function.

如果要在由相似名称标识的一组表上执行函数（例如 purge_this_one_table（tablename）），则可以使用以下构造：

If you want to execute a function (say purge_this_one_table(tablename)) on a group of tables identified by similar names you can use this construction:

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_%');

这篇关于在数组中传递表名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！