How to return values for stored procedure in postgresql?

4.0K 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

Answers (2)

Ranjana

If you’re working with stored procedures in PostgreSQL and need to return values, here’s how you can do it:

1. Understanding Stored Procedures vs. Functions

In PostgreSQL, a stored procedure (CALL procedure_name()) does not return values directly.

If you need to return values, you should either:

Use OUT parameters in a procedure.
Use a function (SELECT function_name()), which can return a value or table.

2. Using OUT Parameters in a Stored Procedure

If you need to return multiple values, use OUT parameters:

CREATE PROCEDURE get_user_details(IN user_id INT, OUT username TEXT, OUT email TEXT)

LANGUAGE plpgsql

AS $$

BEGIN

    SELECT name, email INTO username, email FROM users WHERE id = user_id;

END;

$$;

Calling the procedure:

  CALL get_user_details(1, NULL, NULL);

OUT parameters will hold the returned values.

3. Using a FUNCTION Instead (Preferred for Returning Values)

If you need a single return value or a table, use a function instead:

CREATE FUNCTION get_user_email(user_id INT) RETURNS TEXT AS $$

DECLARE email TEXT;

BEGIN

    SELECT users.email INTO email FROM users WHERE id = user_id;

    RETURN email;

END;

$$ LANGUAGE plpgsql;

Calling the function:

  SELECT get_user_email(1);

Functions can be used in SELECT queries, unlike procedures.

4. Key Takeaways

Stored procedures don’t return values directly—use OUT parameters.
Use functions (RETURNS) if you need to return a value or table.
Functions can be used in SELECT statements, making them more flexible.

2 Weeks

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.

11 Months