How to return values for stored procedure in postgresql?
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
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;Answer (1)
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.