访问复合数组元素plpgsql [英] access composite array elements plpgsql

问题描述

我有一个用户定义的复合数据类型数组.我需要对plpgsql函数中的数组元素进行一些操作，但是我没有正确的语法来访问各个元素.任何帮助表示赞赏.下面粘贴的是该代码的简化版本.

I have a array of user-defined composite data type. I need to do some manipulation on the array elements in plpgsql function but I'm not getting the syntax right to access the individual elements. Any help is appreciated. Pasted below is simplified version of the code.

CREATE TYPE playz AS(
                     a integer,
                     b numeric,
                     c integer,
                     d numeric);

CREATE OR REPLACE FUNCTION playx(OUT mod playz[]) AS $$
BEGIN
   FOR i in 1..5 LOOP
       mod[i].a = 1;
       mod[i].b = 12.2;
       mod[i].c = 1;
       mod[i].d = 0.02;

   END LOOP;
END;
$$ LANGUAGE plpgsql;

尝试执行此操作时出现以下错误.

I get the following error when I try to execute this.

错误:或"附近的语法错误. 第5行:mod [i] .a = 1;

ERROR: syntax error at or near "." LINE 5: mod[i].a = 1;

我正在使用Postgres 9.2

I'm using Postgres 9.2

推荐答案

在PLpgSQL中，左表达式必须非常简单.不支持数组和复合类型的组合.您应该设置一个复合类型的值，然后将该值分配给数组.

The left expressions must be pretty simply in PLpgSQL. The combination of array and composite type is not supported. You should to set a value of composite type, and then this value assign to array.

CREATE OR REPLACE FUNCTION playx(OUT mod playz[]) AS $$
DECLARE r playz;
BEGIN
  FOR i in 1..5 LOOP
    r.a = 1;
    r.b = 12.2;
    r.c = 1;
    r.d = 0.02;
    mod[i] = r;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

可能有一个快捷方式:

CREATE OR REPLACE FUNCTION public.playx(OUT mod playz[])
LANGUAGE plpgsql
AS $function$
BEGIN
  FOR i in 1..5 LOOP
    mod[i] = ROW(1, 12.2, 1, 0.02);
  END LOOP;
END;
$function$;

这篇关于访问复合数组元素plpgsql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！