How to return a result set from a Postgresql function while inserting rows inside a loop?

Question

I'm trying to dynamically generate text values, insert them into a table while checking their uniqueness, and return such values from a function. I managed to do #1 and #2, but I can't find a way for the function to return the generated values. The last try is this one. Returning a table from the function and creating a temp table in the body of the function that's used in the Return clause.

CREATE OR REPLACE FUNCTION add_unique_codes(
    number_of_codes_to_generate integer,
    code_length integer,
    effective_date date,
    expiry_date date)
    RETURNS TABLE(generated_code text) 
    LANGUAGE 'plpgsql'

AS $BODY$
DECLARE
    random_code text := '';
BEGIN
CREATE TEMPORARY TABLE generated_codes(cd text) ON COMMIT DROP;
FOR i IN 1..number_of_codes_to_generate LOOP
    random_code = unique_random_code(code_length, 'p_codes', 'id');
    INSERT INTO p_codes (code, type, effective_date, expiry_date) 
    VALUES (random_code, 'B', effective_date, expiry_date);
    INSERT INTO generated_codes VALUES (random_code);
END LOOP;
RETURN QUERY SELECT cd FROM generated_codes;
END;
$BODY$;

As I said, the function unique_random_code is working fine and I also see the new "codes" inserted in the p_codes table. The only issue is the function doesn't return the set of "codes" back.

Thank you

Answer 1

No need for a temporary table or a slow PL/pgSQL FOR loop. You can use generate_series() to generate the number of rows, and the returning option of the INSERT statement to return those rows:

CREATE OR REPLACE FUNCTION add_unique_codes(
    number_of_codes_to_generate integer,
    code_length integer,
    effective_date date,
    expiry_date date)
    RETURNS TABLE(generated_code text) 
    LANGUAGE sql
AS 
$BODY$
  INSERT INTO p_codes (code, type, effective_date, expiry_date) 
  select unique_random_code(code_length, 'p_codes', 'id'), 'B', effective_date, expiry_date
  from generate_series(1, number_of_codes_to_generate)
  returning code;
$BODY$;