PostgreSQL自定义函数返回复合类型，如何转为多列表格？

作为PostgreSQL开发新手，我尝试编写一个返回数据表的函数，但未能得到正确的多列表格结果。

表定义 #

-- DROP TABLE IF EXISTS dbo.device;

CREATE TABLE IF NOT EXISTS dbo.device
(
    deviceid uuid NOT NULL,
    serialnumber character varying(255) COLLATE pg_catalog."default",
    productcode character varying(255) COLLATE pg_catalog."default",
    description character varying(255) COLLATE pg_catalog."default",
    softwareversion character varying(255) COLLATE pg_catalog."default",
    build character varying(255) COLLATE pg_catalog."default",
    builddate timestamp with time zone,
    assigned boolean,
    groupid uuid,
    updateddatetime timestamp with time zone,
    restartpointerno integer,
    deviceconnectionindex integer,
    organisationid uuid,
    CONSTRAINT pk__device__49e123311461d246 PRIMARY KEY (deviceid),
    CONSTRAINT fk_device_organisation_organisationid FOREIGN KEY (organisationid)
        REFERENCES dbo.organisation (organisationid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT fkdevice951246 FOREIGN KEY (groupid)
        REFERENCES dbo.groups (groupid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT fkdevice_deviceconnectiontype FOREIGN KEY (deviceconnectionindex)
        REFERENCES dbo.deviceconnectiontype (deviceconnectionindex) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS dbo.device
    OWNER to postgres;

函数定义 #

DROP FUNCTION spgetdevicedetails();

CREATE OR REPLACE FUNCTION spgetdevicedetails()
  RETURNS TABLE (ProductCode        text,   -- also visible as OUT param in function body
                 SerialNumber       text,
                 Description        text,
                 SoftwareVersion    text,
                 Build              text,
                 BuildDate          text,
                 Assigned           text
               )
  LANGUAGE plpgsql AS
$func$
#variable_conflict use_column
BEGIN
   RETURN QUERY
        SELECT
            ProductCode ::text,
            SerialNumber ::text ,
            Description ::text,
            SoftwareVersion ::text,
            Build ::text,
            BuildDate ::text,
            Assigned ::text
        FROM 
            dbo.Device;
        
END
$func$;

当前错误结果 #

返回的是单列复合类型：

"(ACSR-3600-A,939029,""Metrology Test Receiver"",1.2.928,4829,,)"
"(TGRF-4024-A,940860,""Office Desk"",1.2.928,4843,,)"
"(TR-3020-A,876302,TR-3020-A,1.4.1,4829,,)"
"(TGRF-4602-A,926371,""Office Bookcase"",1.2.928,4843,,)"

期望结果 #

希望以多列表格形式展示：

解决方法 #

1. 正确调用函数 #

出现复合类型结果的核心原因是调用方式错误。不要直接使用：

SELECT spgetdevicedetails();

而是用以下方式调用，PostgreSQL会自动将返回的表类型展开为多列：

SELECT * FROM spgetdevicedetails();

2. 优化函数定义（可选） #

如果函数不需要plpgsql的流程控制逻辑，可以改用更简洁的SQL函数，同时统一列名大小写（避免#variable_conflict设置）：

DROP FUNCTION IF EXISTS spgetdevicedetails();

CREATE OR REPLACE FUNCTION spgetdevicedetails()
RETURNS TABLE (productcode text,
               serialnumber text,
               description text,
               softwareversion text,
               build text,
               builddate text,
               assigned text)
LANGUAGE sql AS
$func$
  SELECT productcode::text,
         serialnumber::text,
         description::text,
         softwareversion::text,
         build::text,
         builddate::text,
         assigned::text
  FROM dbo.device;
$func$;

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