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.
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
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP