Return multiple tables with different columns in Postgres

Question

I have the following tables which i want to write a function to return data from

Table Definitions

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);

Now I have written a function to try and display this data as follows:

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;

However I get the following error:

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

What am I doing wrong here?

A set-returning function returns a single relation, just like selecting against a view or table would. There is nothing in the docs to suggest you can do what you are trying. Options available to you are to return something dynamic (e,g. JSON as suggested above) or return cursors that could be iterated through separately. — Richard Huxton
– Richard Huxton, Commented Jan 4, 2024 at 14:15

ChrisAsi71 · Accepted Answer · 2024-01-04 16:27:35Z

Ok guys i have come up with the following solution to this

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;

Now i can call this as follows:

With no serial Number:

rollback
BEGIN;

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

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

or with serial number:

rollback
BEGIN;

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

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

Grant Fritchey · Accepted Answer · 2024-01-04 12:29:19Z

-2

From everything I've read, you can't return multiple tables through a function like that. You could insert multiple data sets into a single table, breaking it up by column, or adding a column for the table name. Or, you can return multiple cursors, and load those up as tables. There are several StackOverflow examples of this right here: Postgres function: return multiple tables

answered Jan 4, 2024 at 12:29

Grant Fritchey

2,84125 silver badges25 bronze badges

Collectives™ on Stack Overflow

Return multiple tables with different columns in Postgres

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related