Create function to return results from multiple queries

Question

I am struggling to write a function to return data from more than 1 table in Postgres

CREATE TABLE IF NOT EXISTS dbo.device (
    deviceid uuid NOT NULL,
    serialnumber character varying(255),
    productcode character varying(255),
    description character varying(255),
    softwareversion character varying(255),
    build character varying(255),
    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),
    CONSTRAINT fkdevice951246 FOREIGN KEY (groupid)
        REFERENCES dbo.groups (groupid),
    CONSTRAINT fkdevice_deviceconnectiontype FOREIGN KEY (deviceconnectionindex)
        REFERENCES dbo.deviceconnectiontype (deviceconnectionindex)
);

ALTER TABLE IF EXISTS dbo.device OWNER to postgres;


CREATE TABLE IF NOT EXISTS dbo.devicechannel (
    devicechannelid uuid NOT NULL,
    deviceid uuid,
    groupid uuid,
    channeltypeid uuid,
    derivedchannelid uuid,
    channelid integer,
    channelname character varying(255),
    isinternal boolean,
    dimension character varying(255),
    symbol character varying(255),
    upper real,
    lower real,
    "precision" integer,
    conversiontypeid smallint,
    CONSTRAINT pk__devicech__b0754e8587c246f2 PRIMARY KEY (devicechannelid),
    CONSTRAINT devicechannel_deviceid_channelid UNIQUE (deviceid, channelid),
    CONSTRAINT fkdevice_devicechannel FOREIGN KEY (deviceid)
        REFERENCES dbo.device (deviceid),
    CONSTRAINT fkdevicechan248448 FOREIGN KEY (channeltypeid)
        REFERENCES dbo.channeltype (channeltypeid),
    CONSTRAINT fkdevicechan30310 FOREIGN KEY (conversiontypeid)
        REFERENCES dbo.conversiontype (conversiontypeid),
    CONSTRAINT fkdevicechan3820 FOREIGN KEY (groupid)
        REFERENCES dbo.groups (groupid),
    CONSTRAINT fkdevicechan92763 FOREIGN KEY (derivedchannelid)
        REFERENCES dbo.derivedchannel (derivedchannelid)
);

ALTER TABLE IF EXISTS dbo.devicechannel OWNER to postgres;

This is my function:

CREATE OR REPLACE FUNCTION public.func_test(id integer, ref1 refcursor, ref2 refcursor)
 RETURNS SETOF refcursor
 LANGUAGE plpgsql
AS $function$
BEGIN
    OPEN ref1 FOR SELECT * FROM dbo.Device;
    RETURN NEXT ref1;
    OPEN ref2 FOR SELECT * FROM dbo.DeviceChannel;
    RETURN NEXT ref2;
END;
$function$
;

When I run this as follows:

BEGIN;
    SELECT func_test(69, 'Ref1', 'Ref2');
    FETCH ALL IN "Ref1";
    FETCH ALL IN "Ref2";
COMMIT;

I get no data at all:

But when I run the 2 SELECT statements:

Select * from dbo.Device;
Select * from dbo.DeviceChannel;

They do return data on their own;

How can I write a function in Postgres to use queries which return data from more than 1 table?

Answer 1

The code works as is. Upper case spelling for the portal names is unhelpful, but completely legal. See:

• Are PostgreSQL column names case-sensitive?

The issue seems to be with your client pgAdmin4. It cannot display multiple result sets and only displays the results of the last command. If you execute this code block at once, results from both cursors are discarded, and you only see the "result" from COMMIT;:

BEGIN;
    SELECT func_test(69, 'Ref1', 'Ref2');
    FETCH ALL IN "Ref1";
    FETCH ALL IN "Ref2";
COMMIT;

And there is a bug on top of this in current version 7.7: it shows the table structure of the last FETCH, but with no result rows if you execute all at once.

Solution

Execute each line separately to see results. Or use a different client like psql.

Note that the portals only stay open for the duration of the transaction. Run FETCH ALL IN "Ref1"; and FETCH ALL IN "Ref2"; separately while the transaction is open. Eventually run COMMIT; or ROLLBACK; to close the transaction.

Aside

When passing the portal names to the function in refcursor parameters, you already know the names and don't really need to return them. Just:

CREATE OR REPLACE FUNCTION public.func_test(ref1 refcursor, ref2 refcursor)
  RETURNS void  -- SETOF refcursor  -- noise
  LANGUAGE plpgsql AS
$func$
BEGIN
    OPEN ref1 FOR SELECT * FROM dbo.device;
    -- RETURN NEXT ref1;  -- noise
    OPEN ref2 FOR SELECT * FROM dbo.devicechannel;
    -- RETURN NEXT ref2;  -- noise
END
$func$;

And do you actually need this function at all? It's often simpler to just execute two SELECT statements, or call two (set-returning) functions.