在数组中传递表名

本文介绍了在数组中传递表名的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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