How to return values for stored procedure in postgresql?

3.5K Asked by DavidEdmunds in SQL Server , Asked on Mar 14, 2023

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.

postgresqlstored-procedures

Answered by David Edmunds

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:

SELECT raises exception in PL/pgSQL function

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.

Use PL/pgSQL in PostgreSQL outside of a stored procedure in postgreSQL or function?

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;

Your Answer

Answer (1)

Ranjana

In PostgreSQL, you can return values from a stored procedure using the RETURN statement. Here's an example of how to define and return values from a stored procedure:

CREATE OR REPLACE FUNCTION get_employee_salary(employee_id INT)

RETURNS INT  -- Define the return type

AS $$

DECLARE

    salary INT;

BEGIN

    -- Retrieve the salary for the given employee_id

    SELECT emp_salary INTO salary

    FROM employees

    WHERE emp_id = employee_id;

    -- Return the salary

    RETURN salary;

END;

$$ LANGUAGE plpgsql;

In this example:

We create a stored procedure named get_employee_salary.
The procedure takes one parameter, employee_id, and returns an integer value (the employee's salary).
Within the procedure, we declare a local variable salary to store the retrieved salary value.
We use a SELECT INTO statement to fetch the salary for the given employee_id from the employees table and store it in the salary variable.
Finally, we use the RETURN statement to return the value of salary.

To call this stored procedure and retrieve the returned value, you can use a SQL query like this:

SELECT get_employee_salary(123);

This will execute the stored procedure and return the salary value for the specified employee. If you're executing the procedure from within another PL/pgSQL block or function, you can also capture the returned value using variables.

5 Months