Return id of the deleted entry PostgreSQL-CodePudding

I have a function that delete an entry from my table. Also I need to return the id of the deleted entry.

CREATE OR REPLACE FUNCTION mydb.remove_item(item_id_param text)
RETURNS TABLE(id integer)
LANGUAGE 'plpgsql'

AS $BODY$

BEGIN
RETURN QUERY

    DELETE FROM mydb.items_table
    WHERE item_id = item_id_param;

END;
$BODY$

When I execute the above function, it shows error as;

ERROR:  cannot open DELETE query as cursor
CONTEXT:  PL/pgSQL function mydb.remove_item(text) line 6 at RETURN QUERY

What is wrong in my function?

CodePudding user response：

You need to use the RETURNING clause in order to return the IDs of the deleted rows:

CREATE OR REPLACE FUNCTION mydb.remove_item(item_id_param int)
RETURNS TABLE(id integer)
LANGUAGE plpgsql
AS $BODY$
BEGIN
  RETURN QUERY
      DELETE FROM mydb.items_table
      WHERE item_id = item_id_param
      RETURNING items_table.id; --<< this
END;
$BODY$
;

CodePudding user response：

You do not need plpgsql for this. A simple scalar SQL function will do.

create or replace function mydb.remove_item(item_id_param int) returns int as 
$BODY$
  DELETE FROM mydb.items_table
  WHERE item_id = item_id_param
  RETURNING items_table.id;
$BODY$
language sql;