PL/pgSQL中如何从游标获取复合类型单列到对应变量？

问题场景 #

给定自定义复合类型及PL/pgSQL代码：

CREATE TYPE my_struct AS (
    a int4,
    b text,
    c float4
);

DO $$
DECLARE
    my_cursor refcursor;
    my_value my_struct;
BEGIN
    OPEN my_cursor FOR SELECT (1, 'a', 10.0)::my_struct;
    FETCH my_cursor INTO my_value; -- 执行此处报错
END
$$

执行时PostgreSQL抛出错误：

ERROR: invalid input syntax for type integer: "(1,a,10)"

尝试拆分字段赋值也无效：

FETCH my_cursor INTO my_value.a, my_value.b, my_value.c;

仍触发相同错误。

问题原因 #

你的查询返回的是单个复合类型的列，但直接使用FETCH ... INTO my_value时，PostgreSQL会尝试把这个复合列的文本表示（比如(1,a,10)）赋值给my_value的第一个字段a（整数类型），导致类型转换失败。

正确解决方法 #

有两种可行的方式：

方式1：在游标查询中展开复合类型

修改OPEN语句的查询，将复合类型拆分为单独字段，这样游标返回的列数与复合类型的字段数匹配，可直接赋值给复合变量：

DO $$
DECLARE
    my_cursor refcursor;
    my_value my_struct;
BEGIN
    OPEN my_cursor FOR SELECT ( (1, 'a', 10.0)::my_struct ).*;
    FETCH my_cursor INTO my_value;
    -- 验证结果
    RAISE NOTICE 'my_value: %', my_value;
END
$$

方式2：FETCH时将变量用括号包裹

保持原查询不变，在FETCH时把复合变量用括号包裹，告诉PostgreSQL将游标返回的单个复合列赋值给整个复合变量，而非其第一个字段：

DO $$
DECLARE
    my_cursor refcursor;
    my_value my_struct;
BEGIN
    OPEN my_cursor FOR SELECT (1, 'a', 10.0)::my_struct;
    FETCH my_cursor INTO (my_value); -- 此处添加括号
    -- 验证结果
    RAISE NOTICE 'my_value: %', my_value;
END
$$

处理NULL情况 #

如果查询返回NULL::my_struct，以上两种方式都会将my_value设为NULL，此时my_value IS NULL的判断会返回true，符合需求：

DO $$
DECLARE
    my_cursor refcursor;
    my_value my_struct;
BEGIN
    OPEN my_cursor FOR SELECT NULL::my_struct;
    FETCH my_cursor INTO (my_value);
    IF my_value IS NULL THEN
        RAISE NOTICE 'my_value is NULL';
    END IF;
END
$$

内容的提问来源于stack exchange，提问作者yuri kilochek