Invoke Stored Procedure and Return ID

Question

I have a stored procedure that should do the following:

1. Get next value from sequence
2. Insert into a table
3. Return ID of inserted (step 1)

My stored procedure code is as below:

CREATE OR REPLACE PROCEDURE insert_landed_trades_tp (timestamp without time zone, varchar(200), varchar(200), varchar(200), json) 
LANGUAGE plpgsql
AS $$

DECLARE
    next_id bigint;
BEGIN

    SELECT nextval('trades_landed_tp_row_id_seq') INTO next_id;

    INSERT INTO trades_landed_tp (row_id, capture_dt, trade_guid, feed_name, connector_name, payload, status_code)
    VALUES (next_id, $1, $2, $3, $4, $5, 1);

    COMMIT;

    SELECT next_id;

END;
$$;

I now want to call this stored procedure and consume the ID of the inserted record:

CALL insert_landed_trades_tp (timezone('utc'::text, now()), 'giud'::varchar(200), 'feed'::varchar(200),'connector'::varchar(200), '{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}'::json);

In PgAdmin, the above givees the following error:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead

So, my questions are:

• How do I fix the code to achieve desired outcome?
• Could this be achieved more simply?

Note that once stored procedure development is completed, the procedure would be called from an application (in Python or C#), not from PgAdmin.

Answer 1

Stored procedures cannot use output parameters, only INOUT. Your procedure should look something like this:

CREATE OR REPLACE PROCEDURE insert_landed_trades_tp (
      capture_dt timestamp without time zone, 
      trade_guid varchar(200), 
      feed_name varchar(200), 
      connector_name varchar(200), 
      payload json,
      next_id INOUT bigint) 
LANGUAGE plpgsql
AS $$

BEGIN

    INSERT INTO trades_landed_tp (row_id, capture_dt, trade_guid, feed_name, connector_name, payload, status_code)
    SELECT nextval('trades_landed_tp_row_id_seq'),
           capture_dt,
           trade_guid,
           feed_name,
           connector_name,
           payload,
           1
    RETURNING row_id INTO next_id;

    COMMIT;

END;
$$;


CALL insert_landed_trades_tp (timezone('utc'::text, now()), 'giud'::varchar(200), 'feed'::varchar(200),'connector'::varchar(200), '{ "customer": "John Doe", "items": {"product": "Beer","qty": 6}}'::json, null);
 next_id
---------
      1