How to return multiple inserted rows from a Stored function with insert select statement?

Question

Sample Problem with queries Link

Not able to get any inserted row in the RETURNING statement with INSERT and SELECT command even after adding RETURN NEXT; RETURN ;

SCHEMA FOR USER TABLE

create table "user" (name text not null, updated_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP );

function which needs to be updated to return inserted rows

   CREATE OR REPLACE FUNCTION testFn()
    
    RETURNS table (name character varying , updated_time timestamp without time zone ) AS $$ DECLARE BEGIN
    
    insert into "user" (name , updated_time) 
    select 'alex',now() 
    union 
        select 'alex2',now() 
returning name, updated_time;
    END;
    
    $$ LANGUAGE plpgsql;

This function only inserts into DB but doesnt return the inserted rows with updated time on calling the function

it return no output on

select * from testFn()

Answer 1

It is much simpler to do with a language sql function. Try this (based on the previous version of your function):

CREATE OR REPLACE FUNCTION sp_post_items(i_data json)
 RETURNS table (fulfiller_id text,item_id text, order_id text, status_id integer, item_updated_time timestamp)
AS $function$
 insert into vw_item_status_detail
 (fulfiller_id ,item_id ,order_id , status_id, sku_code, decoration_technology, quantity, item_updated_time)
 select 
    i_data->>'fulfillerId', 
    t->>'itemId', 
    i_data->>'orderId', 
    1000,
    t->>'skuCode',
    t->>'decorationTechnology',
    10,
    now()
  from json_array_elements(i_data  -> 'items') t
 returning fulfiller_id, item_id, order_id, status_id, item_updated_time;
$function$
LANGUAGE sql;

or for the updated function:

CREATE OR REPLACE FUNCTION testFn() 
 RETURNS table (name text, updated_time timestamp without time zone ) AS
$$    
  insert into "user" (name , updated_time) 
    select 'alex',now() 
    union 
    select 'alex2',now() 
  returning name, updated_time;
$$ LANGUAGE sql;

Answer 2

Make it a language sql function:

CREATE OR REPLACE FUNCTION testFn()
  RETURNS table (name character varying , updated_time timestamp without time zone ) 
AS 
$$ 
  insert into "user" (name , updated_time) 
  values 
    ('alex', now()), 
    ('alex2', now())
  returning "user".name, "user".updated_time;
$$ 
LANGUAGE sql;

With a language plpgsql you would need a return query, rather then just putting the insert into it.

CREATE OR REPLACE FUNCTION testFn()
  RETURNS table (name character varying , updated_time timestamp without time zone ) 
AS 
$$ 
begin
  return query
    insert into "user" (name , updated_time) 
    values 
      ('alex', now()), 
      ('alex2', now())
  returning "user".name, "user".updated_time;
end;
$$ 
LANGUAGE plpgsql;

To run the function use:

select *
from testfn();

Online example