PL/pgSQL:访问自定义类型数组的元素的字段 [英] PL/pgSQL: accessing fields of an element of an array of custom type

问题描述

我已经定义了一个自定义类型:

I've defined a custom type:

create type pg_temp.MYTYPE as (f1 int, f2 text);  

然后，在函数或块中，我声明了一个此类数组:

Then, inside a function or a block, I've declared an array of such type:

declare ax MYTYPE[];

我可以通过熟悉的语法ax[1].f1访问元素，但仅用于读取它.
当我使用相同的语法设置字段时，出现语法错误.

I can access an element through the familiar syntax ax[1].f1, but just for reading it.
When I use the same syntax for setting the field, I get a syntax error.

create type pg_temp.MYTYPE as (f1 int, f2 text);

do $$
declare x MYTYPE;
declare ax MYTYPE[];
declare f int;
begin

    x.f1 = 10;
    x.f2 = 'hello';

    --assigning an element: OK
    ax[1] = x;

    --reading an element's field: OK
    f = ax[1].f1;

    --writing an elememt's field: SYNTAX ERROR: 

    ax[1].f1 = f;

end; $$

错误如下:

psql:test.sql:28: ERROR:  syntax error at or near "."
LINE 20:  ax[1].f1 = f;
          ^

我也尝试了语法(ax[1]).f1，结果相同.
哪种正确的语法可以做到这一点?

I've tried also the syntax (ax[1]).f1 with the same result.
Which is the correct syntax to do this?

Postgres服务器版本:9.2.2

Postgres server version: 9.2.2

推荐答案

PLpgSQL有时非常简单，也许太简单了.赋值语句的左侧是一个示例-左侧只能是变量，记录字段或数组字段.不支持任何复杂的左侧部分表达式.​​

PLpgSQL is sometimes very simple, maybe too simple. The left part of assignment statement is a example - on the left can be variable, record field, or array field only. Any complex left part expression are not supported.

您需要数组元素类型的辅助变量.

You need a auxiliary variable of array element type.

DECLARE aux MTYPE;

aux := ax[1];
aux.f := 100000;
ax[1] := aux;

这篇关于PL/pgSQL:访问自定义类型数组的元素的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！