PostgreSql : Not able to retrieve rows after inserting them into DB?

Question

CREATE OR REPLACE FUNCTION sp_post_items(i_data json)
 RETURNS table (fulfiller_id character varying,item_id character varying, order_id character varying, 
 status_id integer, 
-- sku_code character varying, decoration_technology character varying, quantity integer,  
 item_updated_time timestamp without time zone)

AS $function$
begin

 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;
 
-- returning fulfiller_id ,item_id ,order_id , status_id, item_updated_time;
 

end;
$function$
LANGUAGE plpgsql;

Sample Data to run the stored function

select * from post_items('{"orderId": "s",
 "fulfillerId":"kv0fdt6cx7",
"orderDetailsUrl":"het",
"items":[
    {
        "decorationTechnology":"laserEngraving","itemDescription":"Test Sku for Oracle testing",
"itemId":"aagasdsam1",
        "manufacturingUrl":"h-d9ccea00cn-prepress",
        "skuCode":"CIM-QYXB3789","productName":"Tsdacle testing","quantity":"225","taskId":"33a1sd769876c52"
    },
    {
         "decorationTechnology":"ssdas"
         "itemDescription":"Test Sku for satesting",
         "itemId":"aagam2",
         "manufacturingUrl":"httsadfa2d72-f225-4a03-addd-d9ccea00c874~1sadlpen-spress",
         "skuCode":"CXB3789",
         "productName":"Test Sku for Oracle testing",
         "quantity":"225",
         "taskId":"33asad6c52"
    }
]
}'::json)

When I try to run the function values are inserted into DB but in return nothing is recieved.

Expected output To return params mentioned in the return query of the Stored function

Have tried a multiple ways but always get no output

Adrian Klaver · Accepted Answer · 2020-10-12 14:46:51Z

1

You need to add:

...
returning fulfiller_id ,item_id ,order_id , status_id , item_updated_time;
RETURN NEXT;
RETURN;

...

From here:

https://www.postgresql.org/docs/12/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-RETURNING

42.6.1.2. RETURN NEXT and RETURN QUERY

RETURN NEXT and RETURN QUERY do not actually return from the function — they simply append zero or more rows to the function's result set. Execution then continues with the next statement in the PL/pgSQL function. As successive RETURN NEXT or RETURN QUERY commands are executed, the result set is built up. A final RETURN, which should have no argument, causes control to exit the function (or you can just let control reach the end of the function). .

RETURNS table is an alias for RETURNS setof so you need to return a set by using RETURN NEXT.

UPDATE

My mistake. I thought I remembered that RETURN NEXT would automatically pick up the out variables. To make this work you will need to assign the values from RETURNING to the out(table) variables before the RETURN NEXT. As example:

\d names
                       Table "public.names"
 Column |          Type          | Collation | Nullable | Default 
--------+------------------------+-----------+----------+---------
 id     | integer                |           | not null | 
 name   | character varying(200) |           | not null | 
 animal | character varying(200) |           | not null | 
Indexes:
    "names_pkey" PRIMARY KEY, btree (id)

CREATE OR REPLACE FUNCTION public.return_test(id_in integer, name_in character varying, animal_in character varying)
 RETURNS TABLE(id_out integer, name_out character varying, animal_out character varying)
 LANGUAGE plpgsql
AS $function$

BEGIN

    INSERT INTO
        names (id, name, animal)
    VALUES
        (id_in, name_in, animal_in)
    RETURNING id, name, animal INTO id_out, name_out, animal_out;

RETURN NEXT;
RETURN;
END;

$function$


select * from  return_test(1, 'ranger', 'dog');

 id_out | name_out | animal_out 
--------+----------+------------
      1 | ranger   | dog
(1 row)

edited Oct 12, 2020 at 14:46

answered Oct 11, 2020 at 20:30

Adrian Klaver

20.3k3 gold badges24 silver badges40 bronze badges

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

3 Comments

Aagam Doshi Over a year ago

So i need to append - RETURN NEXT; RETURN; - Even after that I get no output

Adrian Klaver Over a year ago

See UPDATE.

Adrian Klaver Over a year ago

Also an FYI on name collisions. You may need to alias the table name in the INSERT and use the alias in the RETURNING clause. Otherwise you may get a duplicate name error with the column names for the TABLE output.

Collectives™ on Stack Overflow

PostgreSql : Not able to retrieve rows after inserting them into DB?

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related