Problem with PLPGSQL function return, uuid involved-CodePudding

I want to assign serial numbers from a sequence, along with uuids that are generated automatically. My table looks like

CREATE TABLE master_serialnumbers
(
    uuident     uuid            PRIMARY KEY DEFAULT uuid_generate_v1(),
    serno       integer         GENERATED ALWAYS AS IDENTITY UNIQUE,
    requester   varchar(20)     NOT NULL
);

I can now generate a serial number with

INSERT INTO master_serialnumbers(requester) VALUES ('XY') 
RETURNING (uuident,serno,requester);

To automate this and generate a block of several serial numbers, I can use a function like this:

CREATE FUNCTION gen_serials_A(num_serials integer, req varchar(20)) 
RETURNS SETOF master_serialnumbers
AS $$
DECLARE
    new_id uuid;
    rr master_serialnumbers%ROWTYPE;        -- or simply RECORD
    tmpSQL TEXT;
BEGIN
    RAISE NOTICE 'generate % serial numbers', num_serials;
    
    CREATE TEMPORARY TABLE tmpIds (id uuid NOT NULL PRIMARY KEY);
    
    FOR i IN 1..num_serials LOOP
        RAISE NOTICE 'serno %', i;
        INSERT INTO master_serialnumbers(space, requester) VALUES (spc, req) 
            RETURNING (uuident) INTO new_id;
        INSERT INTO tmpIds(id) VALUES (new_id);
    END LOOP;

    FOR rr IN EXECUTE 'SELECT * FROM master_serialnumbers 
                       WHERE uuident IN (SELECT id FROM tmpIds)' LOOP
        RETURN NEXT rr;
    END LOOP;

    tmpSQL := 'DROP TABLE tmpIds';
    EXECUTE tmpSQL;

RETURN;
END
$$ LANGUAGE plpgsql;

This does work, but it seems cumbersome to store the values in a temporary table and have two loops. So I tried to shorten it like this:

CREATE FUNCTION gen_serials(num_serials integer, req varchar(20)) 
RETURNS SETOF master_serialnumbers
AS $$
DECLARE
    rr master_serialnumbers%ROWTYPE;
BEGIN
    RAISE NOTICE 'generate % serial numbers', num_serials;
    
    FOR i IN 1..num_serials LOOP
        RAISE NOTICE 'serno %', i;
        INSERT INTO master_serialnumbers(requester) VALUES (req)
            RETURNING (uuident,serno,requester) INTO rr;
        RAISE NOTICE 'rr is %', rr;
        RETURN NEXT rr;
    END LOOP;

RETURN;
END
$$ LANGUAGE plpgsql;

– but the INSERT throws an error

invalid input syntax for type uuid: »(12345678-1234-1234-1234-123456789abc,1,A)«

Changing the declaration to rr RECORD now passes the INSERT:

rr is ("(12345678-1234-1234-1234-123456789abc,1,A)")
ERROR:  wrong record type supplied in RETURN NEXT
DETAIL:  Returned type does not match expected type uuid in column 1.

Apparently all has been converted into a string.

And changing the return type also to SETOF RECORD yields another error:

ERROR: function with set result called in a context that cannot process set results

(German error messages translated back to English by me, sorry for that).

Any ideas what's (not) happening here? Perhaps I am doing it much too long-winded at all by using a function with procedural loops?

CodePudding user response：

To automate this and generate a block of several serial numbers, I can use a function

Don't make it so complicated. Use something like

INSERT INTO master_serialnumbers(requester)
VALUES ('XY', 'YZ', 'ZA') 
RETURNING uuident, serno, requester;

INSERT INTO master_serialnumbers(requester)
SELECT UNNEST(ARRAY['XY', 'YZ', 'ZA']) -- useful for parameterised queries
RETURNING uuident, serno, requester;

INSERT INTO master_serialnumbers(requester)
SELECT 'XY'
FROM generate_series(1, 20) -- useful for arbitrary repetition of the same value
RETURNING uuident, serno, requester;

(a set-returning function to create a series of numbers)