10

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
  • 2
    If you want to return something use a function, procedures aren't meant to return something. DZone is wrong, a function can return a result set, not just a single value Jan 16, 2020 at 18:55
  • Thank you so much Sir Erwin Brandstetter, for improving the question's formatting and syntax, making it more readable and ethical, really appreciated and too thank you for devoting your time reading and improving it, Thank you once again. Jul 22, 2020 at 18:11

2 Answers 2

8

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 (or OUT mode since in Postgres 14). zsheep provided an example.

Consider a DO statement to run ad-hoc PL/pgSQL 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.
In a PL/pgSQL procedure, 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$;

In an SQL procedure the last command returning values determines the return value:

CREATE PROCEDURE lang_sql_demo(INOUT _val text DEFAULT null)
  LANGUAGE sql AS
$proc$
SELECT val FROM tbl WHERE id = 2;
$proc$;

Similar in a standard SQL procedure:

CREATE OR REPLACE PROCEDURE lang_std_sql_demo(INOUT _val text DEFAULT null)
  LANGUAGE sql
BEGIN ATOMIC
SELECT val FROM tbl WHERE id = 2;
END;

See:

db<>fiddle here

Related:

2
  • What is the difference between PL/pgSQL and SQL and standard SQL?
    – J Brune
    Aug 11 at 21:21
  • @JBrune: "SQL procedures" and "standard SQL procedures" use the same SQL language. But traditional Postgres functions and procedures use different syntax for the routine declaration and also handle the body differently (mostly early binding vs. late binding). PL/pgSQL is the procedurtal language extension that adds procedural elements and mostly serves as glue to execute nested SQL statements. Follow the many links I provided, this is beyond the scope of a comment. Aug 12 at 21:55
9

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...

1
  • Why are you using scalar? Can you use a row set? Jun 11 at 16:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.