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!

Postgres doesn't distinguish between a function and procedure. Basically, you could write a function that all these (updates/inserts etc) and use select to run it in a sql scope, within a transaction or use `perform` to run it inside plpgsql block. Here is an example of a function that returns refcursor: https://stackoverflow.com/questions/6674787/calling-a-function-that-returns-a-refcursor — Kaushik Nayak, Jun 20 '18 at 05:25
thanks for your input, im already able to run a function that returns a refcuror using the "SELECT ". Btw, procedure uses "CALL " and the syntax in the create procedure script is different from a function — deviantxdes, Jun 20 '18 at 05:38
@KaushikNayak: Postgres 11 **will** distinguish between functions and procedures — , Jun 20 '18 at 05:39
@a_horse_with_no_name : Didn't know that. Thanks for the info. I'm new to Postgres — Kaushik Nayak, Jun 20 '18 at 05:43

Laurenz Albe · Accepted Answer · 2018-06-20T07:16:38.027

8

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;

edited Jun 20 '18 at 07:16

answered Jun 20 '18 at 05:51

Laurenz Albe

209,280
17
206
263

Hmm are you able to construct an example ? in the official doc it was mentioned that "(OUT arguments are currently not supported for procedures. Use INOUT instead.)". – deviantxdes Jun 20 '18 at 05:54
True; I have removed the reference to `INOUT` parameters. About examples: it works just like it does in functions today. – Laurenz Albe Jun 20 '18 at 06:04
thanks for your input, do u have any idea if INOUT supports the refcursor type? For functions that would be e.g. "OUT results refcursor" – deviantxdes Jun 20 '18 at 06:50
and also how will the procedure with inout refcursor be executed? Call ('refcuror') – deviantxdes Jun 20 '18 at 07:07
I have added an example that should show you how it works. – Laurenz Albe Jun 20 '18 at 07:16
Thanks for the example, really appreciate it. May i know what IDE are you using to execute the procedure? iam using DBeaver and it seems that running "CALL testproc(null);" does not return any results/refcursor at all. Thus unable to perform the "fetch all in.." – deviantxdes Jun 20 '18 at 07:36
My IDE is `psql`, and I can only recommend it. The statement should definitely return a row just like a `SELECT` statement would. Are you using v11? The `FETCH` should work even if you don't see the result (it will always be `cur`). – Laurenz Albe Jun 20 '18 at 07:40
Oh wow, it really does work with psql... @Laurenz Albe, May i ask if the VALUES can be of a row/record instead of a number/string e.g. " OPEN r for values (select fid,fname from user_player limit 1);" – deviantxdes Jun 20 '18 at 08:03
Yes, you can check that. – Laurenz Albe Jun 20 '18 at 08:03
How may i read the multiple INOUT values? say i have "INOUT page bigint" and a "INOUT results refcursor". . Calling "FETCH ALL IN cur;" only returns me whats in the refcursor, how may i get the values of page together? – deviantxdes Jun 21 '18 at 06:20
I don't understand that question. You get both `INOUT` parameters in the result row from the `CALL` statement. Play with it, you'll see it is simple. – Laurenz Albe Jun 21 '18 at 06:27
Sorry for the bad question, this is an example create script "CREATE OR REPLACE PROCEDURE pg_player_get_by_id3( IN pid character, INOUT page bigint, INOUT results refcursor)" ..... how can i read the output of 'page' as well as 'results' ? I have tried calling "FETCH ALL IN cur" but it does not shows the output for 'page' – deviantxdes Jun 21 '18 at 06:30
No. Before you `FETCH`, you have to `CALL` the procedure, right? And that `CALL` will return both `page` and `results`. I believe this is just a thinko on your side. – Laurenz Albe Jun 21 '18 at 06:33

score 0 · Answer 2 · answered Jun 20 '18 at 05:10

0

How about using the RAISE statement?

https://www.postgresql.org/docs/10/static/plpgsql-errors-and-messages.html

answered Jun 20 '18 at 05:10

isapir

21,295
13
115
116

Not all response is an error/exception, there could be scenarios where i need to return the number of rows that are affected, etc. – deviantxdes Jun 20 '18 at 05:39

PostgreSQL 11 - Procedures

2 Answers2

Linked