TransWikia.com

Returning values for Stored Procedures in PostgreSQL

Database Administrators Asked by purav desai on December 4, 2021

I was reading this on PostgreSQL Tutorials:

In case you want to return a value from a stored procedure, you can use output parameters. The final values of the output parameters will be returned to the caller.

And then I found a difference between function and stored procedure at DZone:

Stored procedures do not return a value, but stored functions return a single value

Can anyone please help me resolve this.

If we can return anything from stored procedures, please also let me know how to do that from a SELECT statement inside the body.

If I am wrong somewhere please inform.

2 Answers

Both your sources are plain wrong.

  • A FUNCTION basically always returns something. void at the minimum, a single value, a row, or a set of rows ("set-returning function", a.k.a. "table-function") - the only variant that can return nothing, i.e. no row. Called from within plpgsql code, you have to actively dismiss results if you don't want them. See:

  • A PROCEDURE (Postgres 11 or later) returns a single row if it has any arguments with the INOUT mode. zsheep already provided an example.

Consider a DO statement to run ad-hoc plpgsql code without passing or returning anything.

To address your core question:

If we can return anything from stored procedures, please also let me know how to do that from a SELECT statement inside the body.

The same way as in functions, as that's in the realm of general PL/pgSQL: assign to the parameter with the INTO keyword:

CREATE PROCEDURE assign_demo(INOUT _val text DEFAULT null)
  LANGUAGE plpgsql AS
$proc$
BEGIN
   SELECT val FROM tbl WHERE id = 2
   INTO _val;                              -- !!!
END
$proc$;

db<>fiddle here

Related:

Answered by Erwin Brandstetter on December 4, 2021

Procedure are a new thing in Postgresql as of version 11, there are two big differences between Procedures and Functions

One Procedures can issue a commit or rollback and keep processing, Functions can not issue a commit or rollback.

Functions create an implicit transaction any exception that occurs will cause a rollback, unless there is an Exception Block

Procedure can return a value via INOUT argument see create functions how that works

create procedure increase(inout i integer )
LANGUAGE 'plpgsql'
AS $$
begin
    i = i+1;
    return;
end ;
$$;

call increase(1);

2nd biggest difference is

select increase () -- fails with an error 
-- must use CALL to use a procedure
call increase (3) ;

Unless there is a need to commit or rollback use Functions, there are more features for calling and return values...

Answered by zsheep on December 4, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP