循环遍历 PL/pgSQL 中的给定值列表 [英] Looping through a given list of values in PL/pgSQL

问题描述

我正在尝试遍历几个字段并对它们运行一个函数:

FOR 字段 IN ARRAY['f1','f2'] LOOP执行 pg_temp.converFieldToLower(newTableNameRaw,field)结束循环；

这是我尝试使用的功能:

创建或替换函数 pg_temp.converFieldToLower(t varchar, f varchar) RETURNS void AS $$#variable_conflict use_variable开始EXECUTE concat_ws (' ', 'UPDATE',t,'SET',f,'= LOWER(',f,')');结尾;$$ 语言 plpgsql;

这似乎不是声明数组的正确方式，我做错了什么?

<块引用>

错误:ARRAY"处或附近的语法错误第 49 行: FOR 字段 IN ARRAY['f1','f2'] LOOP

解决方案

FOREACH 循环 专门用于遍历数组值的元素，例如:

FOREACH 字段 IN ARRAY ARRAY['f1','f2'] LOOP执行 pg_temp.converFieldToLower(newTableNameRaw,field) into res;结束循环；

该功能是在 Postgres 9.1 中引入的.

I'm trying to loop through few fields and run a function on them:

FOR field IN ARRAY['f1','f2'] LOOP
    execute pg_temp.converFieldToLower(newTableNameRaw,field)
END LOOP;

This is the function i'm trying to use:

CREATE OR REPLACE FUNCTION pg_temp.converFieldToLower(t varchar, f varchar) RETURNS void AS $$
#variable_conflict use_variable
BEGIN
  EXECUTE concat_ws (' ', 'UPDATE',t,'SET',f,'= LOWER(',f,')');
END;
$$ LANGUAGE plpgsql;

It looks like it's not the right way to declare an array, what am I doing wrong?

ERROR:  syntax error at or near "ARRAY"
LINE 49:         FOR field IN ARRAY['f1','f2'] LOOP

解决方案

The FOREACH loop is designed specifically for iterating through the elements of an array value, e.g.:

FOREACH field IN ARRAY ARRAY['f1','f2'] LOOP
    execute pg_temp.converFieldToLower(newTableNameRaw,field) into res;
END LOOP;

The feature was introduced in Postgres 9.1.

这篇关于循环遍历 PL/pgSQL 中的给定值列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！