postgresql 在Postgres中返回具有不同列的多个表

我有下面的表，我想写一个函数来返回数据

表格定义

create table device
(
    deviceid              uuid not null
        constraint pk__device__49e123311461d246
            primary key,
    serialnumber          varchar(255),
    productcode           varchar(255),
    description           varchar(255),
    softwareversion       varchar(255),
    build                 varchar(255),
    builddate             timestamp with time zone,
    assigned              boolean,
    groupid               uuid
        constraint fkdevice951246
            references public.groups,
    updateddatetime       timestamp with time zone,
    restartpointerno      integer,
    deviceconnectionindex integer
        constraint fkdevice_deviceconnectiontype
            references public.deviceconnectiontype,
    organisationid        uuid
        constraint fk_device_organisation_organisationid
            references public.organisation
);

alter table device
    owner to postgres;
create table groups
(
    groupid       uuid not null
        constraint pk__groups__149af30a383c48d4
            primary key,
    groupname     varchar(255),
    groupparentid uuid
        constraint fkgroups635827
            references groups
);

alter table groups
    owner to postgres;

INSERT INTO public.groups (groupid, groupname, groupparentid) VALUES ('6071392c-ca4a-4967-9ec2-f75e222d8b41', 'CallibrationLab', NULL);
INSERT INTO public.groups (groupid, groupname, groupparentid) VALUES ('68c1e75c-4d80-45f1-ac9c-766b53a1271e', 'Echo chamber', NULL);
INSERT INTO public.groups (groupid, groupname, groupparentid) VALUES ('32c82a28-632f-4e60-a7ab-a1ca86c93cff', 'DaveTest', NULL);
INSERT INTO public.groups (groupid, groupname, groupparentid) VALUES ('8c4232be-c054-4ed8-bb66-0c9616c301cd', 'Tim Test', '6071392c-ca4a-4967-9ec2-f75e222d8b41');
INSERT INTO public.groups (groupid, groupname, groupparentid) VALUES ('89c49f24-2a34-4517-a7e4-fb81a52d82dd', 'Thunder Chamber', NULL);
INSERT INTO public.groups (groupid, groupname, groupparentid) VALUES ('6139a2ab-4029-4188-b665-3d4064bababa', 'SuperGran', '6071392c-ca4a-4967-9ec2-f75e222d8b41');
INSERT INTO public.groups (groupid, groupname, groupparentid) VALUES ('ea0db9be-edb2-4af1-ad6a-1cf7e8b9fa06', 'George Test', NULL);

INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('04a2feec-d61d-49cd-88df-dadb3aa66e6c', '926371', 'TGRF-4602-A', 'Office Bookcase', '1.2.928', '4843', NULL, NULL, NULL, '2023-09-29 11:18:24.526667+01', 3050536, 2, NULL);
INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('189a3642-64ca-4bf8-ae58-0e1f0ef27d22', '939029', 'ACSR-3600-A', 'Metrology Test Receiver', '1.2.928', '4829', NULL, NULL, '89c49f24-2a34-4517-a7e4-fb81a52d82dd', '2023-09-29 11:18:24.52+01', 9060068, 1, NULL);
INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('7315a69e-1dad-4c5b-bf8e-5433b90b62c6', '876302', 'TR-3020-A', 'TR-3020-A', '1.4.1', '4829', NULL, NULL, NULL, '2023-09-29 11:18:24.666667+01', 4683540, 2, '272ef7b7-244b-4911-a011-d941774028c3');
INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('51a64e1f-c7b4-4ea8-bfaf-2fdff527b751', '940860', 'TGRF-4024-A', 'Office Desk', '1.2.928', '4843', NULL, NULL, NULL, '2023-09-29 11:18:24.506667+01', 8870307, 2, NULL);
INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('95e1610d-e3f7-42fc-8303-9da9b9aaf784', '545415', 'TGRF-4026-A', 'Test Logger3', '1.2.3', '4848', NULL, NULL, NULL, NULL, 7341269, NULL, NULL);
INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('7036b270-1ddd-4ce6-aef0-9b8508db1c6b', '856365', 'TGRF-4024-A', 'Test Logger1', '1.2.3', '4848', NULL, NULL, NULL, NULL, 461912, NULL, NULL);
INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('c525e43d-a53b-41da-a9ef-a971a2dd14ab', '455455', 'TGRF-4025-A', 'Test Logger2', '1.2.3', '4848', NULL, NULL, NULL, NULL, 2572984, NULL, NULL);
INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('ba5ca162-95ba-4fc7-9335-710af1d3c783', '737675', 'TK-4014', 'Test Logger2', '1.2.3', '4848', NULL, NULL, NULL, NULL, 1587554, NULL, NULL);
INSERT INTO public.device (deviceid, serialnumber, productcode, description, softwareversion, build, builddate, assigned, groupid, updateddatetime, restartpointerno, deviceconnectionindex, organisationid) VALUES ('ed86ef25-e131-4591-98ae-11f4cfca448d', '894339', 'TR-3020-A', 'TR-3020-A', NULL, NULL, NULL, NULL, NULL, NULL, 6017319, NULL, NULL);

字符串
现在我写了一个函数来尝试显示这些数据，如下所示：

CREATE OR REPLACE FUNCTION get_multiple_tables()
RETURNS SETOF TABLE (
    DeviceID uuid,
    SerialNumber varchar(255)
),TABLE (
    GroupID uuid,
    GroupName varchar(255),
    GroupParentID uuid
)
AS $$
BEGIN
    -- Your logic to populate the tables goes here
    -- You can use SELECT statements or other operations
    -- Returning values for table 1
    RETURN NEXT (SELECT  DeviceID, SerialNumber FROM device);
    -- Returning values for table 2
    RETURN NEXT (SELECT GroupID, GroupName, GroupParentID FROM groups);
END;
$$ LANGUAGE plpgsql;

型
但我得到以下错误：

[2024-01-04 12:10:12] [42601] ERROR: syntax error at or near "TABLE"
[2024-01-04 12:10:12] Position: 65

型
我做错了什么？

好了，伙计们，我已经想出了以下解决方案

DROP FUNCTION IF EXISTS spgetdeviceinventory_main(text, refcursor, refcursor, refcursor);

create function spgetdeviceinventory_main(serialnumber2 text, ref1 refcursor, ref2 refcursor, ref3 refcursor) returns SETOF refcursor
    language plpgsql
as
$$
BEGIN

    IF serialnumber2 = '' then
              OPEN ref1 FOR SELECT  DeviceID, SerialNumber, ProductCode FROM device ;   -- Open the first cursor
              RETURN NEXT ref1;                                                                              -- Return the cursor to the caller

              OPEN ref2 FOR SELECT GroupID, GroupName, GroupParentID  FROM groups;   -- Open the second cursor
              RETURN NEXT ref2;

              OPEN ref3 FOR

                  SELECT
                        DeviceChannelID,
                        DeviceID,
                        GroupID,
                         dc.ChannelTypeID,
                        DerivedChannelID,
                        ChannelID,
                        ChannelName,
                        IsInternal,
                        Dimension,
                        Symbol,
                        Upper,
                        Lower,
                        Precision,
                        ct.ChannelTypeIndex
                    FROM
                        DeviceChannel dc
                           left join
                        ChannelType ct on dc.ChannelTypeID = ct.ChannelTypeID;

              RETURN NEXT ref3;
    ELSE
         OPEN ref1 FOR SELECT  DeviceID, SerialNumber, ProductCode FROM device where serialnumber = serialnumber2;   -- Open the first cursor
              RETURN NEXT ref1;                                                                              -- Return the cursor to the caller

              OPEN ref2 FOR
                        SELECT d.GroupID, GroupName,GroupParentID
                        FROM
                            groups g
                        JOIN
                            device d on g.GroupID = d.GroupID
                        WHERE
                            d.SerialNumber = serialnumber2;
           -- Open the second cursor


              RETURN NEXT ref2;

              OPEN ref3 FOR

                  SELECT
                        DeviceChannelID,
                        d.DeviceID,
                        d.GroupID,
                         dc.ChannelTypeID,
                        DerivedChannelID,
                        ChannelID,
                        ChannelName,
                        IsInternal,
                        Dimension,
                        Symbol,
                        Upper,
                        Lower,
                        Precision,
                        ct.ChannelTypeIndex
                    FROM
                        DeviceChannel dc
                    LEFT JOIN
                        ChannelType ct on dc.ChannelTypeID = ct.ChannelTypeID
                    JOIN
                        device d on dc.deviceid = d.deviceid
                    where
                        d.serialnumber = serialnumber2;

              RETURN NEXT ref3;

    end if;

      -- Return the cursor to the caller
    END;
$$;

alter function spgetdeviceinventory_main(text, refcursor, refcursor, refcursor) owner to postgres;

字符串
现在我可以这样称呼它：
无序列号：

rollback
BEGIN;

   SELECT spGetDeviceInventory_Main('','S1', 'S2','S3');

   FETCH ALL IN "S1";
   FETCH ALL IN "S2";
   FETCH ALL IN "S3";
   COMMIT;

型
或序列号：

rollback
BEGIN;

   SELECT spGetDeviceInventory_Main('926371','S1', 'S2','S3');

   FETCH ALL IN "S1";
   FETCH ALL IN "S2";
   FETCH ALL IN "S3";
   COMMIT;

型

postgresql 在Postgres中返回具有不同列的多个表

2条答案

相关问题

热门标签

最新问答