Revision history [back]

Yes, it is possible to perform a PostgreSQL stored procedure call and fetch query simultaneously using the INOUT parameter.

Here's an example:

CREATE OR REPLACE FUNCTION get_employee_details(INOUT emp_id INTEGER, OUT emp_name VARCHAR, OUT emp_salary NUMERIC) 
LANGUAGE plpgsql
AS $$
BEGIN
     SELECT name, salary INTO  emp_name, emp_salary FROM employees WHERE id = emp_id;

     SELECT count(*) INTO emp_id FROM employees WHERE salary > emp_salary;
END;
$$;

DECLARE
    emp_id INTEGER := 1;
    emp_name VARCHAR;
    emp_salary NUMERIC;
BEGIN
    SELECT emp_id, emp_name, emp_salary 
    FROM get_employee_details(emp_id, emp_name, emp_salary);

    -- Do something with the results
END;

In this example, we have defined a stored procedure get_employee_details that takes an INOUT parameter emp_id and two OUT parameters emp_name and emp_salary.

Inside the stored procedure, we first fetch the name and salary of the employee with the given emp_id and assign them to the emp_name and emp_salary parameters.

Then, we run another query to count the number of employees whose salary is greater than the fetched employee's salary and assign the count to the same emp_id parameter.

Finally, in the main program, we call the stored procedure by passing the emp_id, emp_name, and emp_salary variables as arguments. The stored procedure modifies the emp_id parameter, which is then used to fetch the results and do something with them.