How to call a stored procedure (not function) with INOUT parameter in PostgreSQL 13

Question

I have this stored procedure :

CREATE OR REPLACE PROCEDURE SP_ObtenerSecuencialFactura(INOUT p_secuencial INT)
LANGUAGE PLPGSQL 
AS 
$$
BEGIN
    SELECT MAX("CODIGOFACTURA") + 1 INTO p_secuencial FROM "FACTURA";
    IF p_secuencial IS NULL THEN
        p_secuencial := 1;
    END IF;
END
$$

And the calling:

DECLARE secuencial INT;
CALL SP_ObtenerSecuencialFactura(secuencial);
RAISE NOTICE '%', secuencial;

But I get this error when I call that stored procedure:

ERROR: syntax error at or near "INT"

LINE 1: DECLARE secuencial INT;

What's wrong? I was finding examples but only exist with functions.

score 4 · Answer 1 · answered Oct 10 '20 at 21:26

This is the solution:

DO
$$
DECLARE secuencial INT;
BEGIN
    CALL SP_ObtenerSecuencialFactura(secuencial);
    RAISE NOTICE '%', secuencial;
END
$$

NOTICE: 1

DO

Query returned successfully in 85 msec.

PostgreSQL use PL/pgSQL like Oracle with PL/SQL, so, to call a Store Procedure with OUTIN parameter, we need envolved the calling and the variable in Anonymous Block with "do" and "$$"

DO in PostgreSQL

How to call a stored procedure (not function) with INOUT parameter in PostgreSQL 13

1 Answers1