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

Question

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()

@ a_horse_with_no_name the link in the description has the complete code here — Aagam Doshi
– Aagam Doshi, Commented Oct 12, 2020 at 7:26
@ a_horse_with_no_name tried to update the question properly with schema — Aagam Doshi
– Aagam Doshi, Commented Oct 12, 2020 at 8:57

score 2 · Accepted Answer · 2020-10-12 09:08:30Z

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

edited Oct 12, 2020 at 9:08

answered Oct 12, 2020 at 9:00

user330315

Sign up to request clarification or add additional context in comments.

2 Comments

Aagam Doshi Over a year ago

tried this, it doesn't return anything - I am using insert into select statement as the input needs to be extracted from json. Hope that might not be the cause of the issue

user330315 Over a year ago

@AagamDoshi: yes it does: dbfiddle.uk/…

Stefanov.sm · Accepted Answer · 2020-10-12 09:00:07Z

0

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;

edited Oct 12, 2020 at 9:00

answered Oct 12, 2020 at 8:57

Stefanov.sm

13.2k2 gold badges24 silver badges24 bronze badges

5 Comments

Aagam Doshi Over a year ago

this doesn't returns anything

Stefanov.sm Over a year ago

@AagamDoshi How did you invoke it? It returns 2 rows with 2 columns. Did you try select * from testFn()?

Aagam Doshi Over a year ago

select * from testFn() @Stefanov.sm

Aagam Doshi Over a year ago

Yes it works for the example !! I am not sure why its not working for my exact use case for sp_post_items, is it because in the example i am inserting into table and my exact usecase I am inserting into a View?? @Stefanov.sm

Stefanov.sm Over a year ago

@AagamDoshi I do not think that a view would make a difference. However I have not tried to see what would happen if the view has an instead of insert trigger.

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related