PostgreSQL 11 - Procedures

Question

With the latest update of PostgreSQL supporting procedures. The official blog, quoted that "As opposed to functions, procedures are not required to return a value." (https://blog.2ndquadrant.com/postgresql-11-server-side-procedures-part-1/)

So my question is, is there actually any way for me to return error code or response in a procedure? (Procedures is rather new in Postgres thus there were very little resources online.)

Here is an example of what I meant by returning these "error codes"

create or replace PROCEDURE multislot_Update_v1
(
  p_id in varchar2,
  p_name in varchar2,
  p_enname in varchar2,
  results out SYS_REFCURSOR
) AS
rowNumber int;
defaultNumber int;
BEGIN

   select count(1) into rowNumber from MULTISLOTSGAME where fid=P_id;

    if (rowNumber = 0) then
      open results for
      select '1' as result from dual;
      return;
    end if;

  update MULTISLOTSGAME  set
    name = P_name,
    enname = P_enname
  where fid = P_id ;
  commit;

 open results for
  select '0' as result, t1.* from MULTISLOTSGAME t1 where fid = p_id;

END multislot_Update_v1;

The above script is an Oracle procedure, as u can see if the returned result is "1" it meant that the update wasn't successful.

Is there any way I can write the above script (with error code) as a PostgresSQL Procedure ? Maybe an example of using the "INOUT" argument would be great!

Answer 1

You can have INOUT parameters in a procedure.

You call a procedure with the CALL statement; if there are any INOUT parameters, the statement will return a result row just like SELECT.

Here is an example that uses a procedure that returns a refcursor:

CREATE PROCEDURE testproc(INOUT r refcursor) LANGUAGE plpgsql AS
$$BEGIN
   r := 'cur';
   OPEN r FOR VALUES (1), (42), (12321);
END;$$;

BEGIN;

CALL testproc(NULL);

  r  
-----
 cur
(1 row)

FETCH ALL FROM cur;

 column1 
---------
       1
      42
   12321
(3 rows)

COMMIT;