Question 313888 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Return multiple tables with different columns in Postgres

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?

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    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;

  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search