Q:

Get the identity from stored procedure

My procedure :
CREATE PROCEDURE MyInsert(_sno integer, _eid integer, _sd date, _ed date, _sid integer, _status boolean)
LANGUAGE SQL
AS $BODY$
    INSERT INTO app_for_leave(sno, eid, sd, ed, sid, status)
    VALUES(_sno, _eid, _sd, _ed, _sid, _status);   
$BODY$;

i need to  get the output parameter vlaue inside another procedure in Postgresql 11.
 CALL MyInsert(1,101,'2013-04-04','2013-04-04',2,'f' );

How to get the Return value from another procedure.

A:

The natural thing would be to use a function:
CREATE FUNCTION myinsert(
   IN _sno integer,
   IN _eid integer,
   IN _sd date,
   IN _ed date,
   IN _sid integer,
   IN _status boolean,
   OUT _id bigint
) LANGUAGE sql
AS $BODY$
    INSERT INTO app_for_leave(sno, eid, sd, ed, sid, status)
    VALUES(_sno, _eid, _sd, _ed, _sid, _status)
    RETURNING id;   
$BODY$;

From PL/pgSQL code you'd call it like this:
SELECT myinsert(1, 1, current_date, current_date, 1, FALSE)
INTO _id;

If you insist in a procedure, you can do it like this:
CREATE PROCEDURE myinsert(
   IN _sno integer,
   IN _eid integer,
   IN _sd date,
   IN _ed date,
   IN _sid integer,
   IN _status boolean,
   INOUT _id bigint
) LANGUAGE sql
AS $BODY$
    INSERT INTO app_for_leave(sno, eid, sd, ed, sid, status)
    VALUES(_sno, _eid, _sd, _ed, _sid, _status)
    RETURNING id;   
$BODY$;

and call it like this:
CALL myinsert(1, 1, current_date, current_date, 1, FALSE, _id);

Note that for the procedure it would be natural to define an OUT parameter, but PostgreSQL procedures don't support OUT parameters yet.

