OUT parameters in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

What is the output of this function call with OUT parameters?

Consider the following PostgreSQL function:

CREATE OR REPLACE FUNCTION get_employee_info(emp_id INT, OUT emp_name TEXT, OUT emp_salary NUMERIC) AS $$
BEGIN
  SELECT name, salary INTO emp_name, emp_salary FROM employees WHERE id = emp_id;
END;
$$ LANGUAGE plpgsql;

What will be the result of SELECT * FROM get_employee_info(3); if the employee with id=3 is named 'Alice' with salary 75000?

PostgreSQL

CREATE OR REPLACE FUNCTION get_employee_info(emp_id INT, OUT emp_name TEXT, OUT emp_salary NUMERIC) AS $$
BEGIN
  SELECT name, salary INTO emp_name, emp_salary FROM employees WHERE id = emp_id;
END;
$$ LANGUAGE plpgsql;

A

| get_employee_info |
|-------------------|
| (Alice,75000)     |

B

| emp_name | emp_salary |
|----------|------------|
| Alice    | 75000      |

C

| emp_name | emp_salary |
|----------|------------|
| NULL     | NULL       |

D ERROR: function get_employee_info(integer) does not exist

💡 Hint

OUT parameters in PostgreSQL functions return a row with named columns.

The function uses OUT parameters, so calling SELECT * FROM get_employee_info(3); returns a row with columns emp_name and emp_salary populated with the employee's data.

🧠 Conceptual

intermediate

What is the main difference between OUT parameters and RETURNS TABLE in PostgreSQL functions?

Choose the correct statement about OUT parameters versus RETURNS TABLE in PostgreSQL functions.

A OUT parameters can only return one value; RETURNS TABLE can return multiple rows.

B OUT parameters require a RETURN statement; RETURNS TABLE does not.

C OUT parameters define output variables and implicitly create a result set; RETURNS TABLE explicitly defines the result columns and types.

D OUT parameters are deprecated and should not be used; RETURNS TABLE is the modern replacement.

💡 Hint

Think about how output columns are defined and returned in each method.

OUT parameters declare output variables that become columns in the result set implicitly. RETURNS TABLE explicitly declares the output columns and their types.

📝 Syntax

advanced

Which function definition correctly uses OUT parameters to return two values?

Identify the correct PostgreSQL function syntax that uses OUT parameters to return a user's first and last name.

A CREATE FUNCTION get_names(user_id INT, OUT first_name TEXT, OUT last_name TEXT) RETURNS RECORD AS $$ BEGIN SELECT fname, lname INTO first_name, last_name FROM users WHERE id = user_id; END; $$ LANGUAGE plpgsql;

B CREATE FUNCTION get_names(user_id INT) RETURNS TABLE(first_name TEXT, last_name TEXT) AS $$ BEGIN SELECT fname, lname FROM users WHERE id = user_id; END; $$ LANGUAGE plpgsql;

C CREATE FUNCTION get_names(user_id INT, OUT first_name TEXT, OUT last_name TEXT) AS $$ BEGIN SELECT fname, lname FROM users WHERE id = user_id INTO first_name, last_name; END; $$ LANGUAGE plpgsql;

D CREATE FUNCTION get_names(user_id INT, OUT first_name TEXT, OUT last_name TEXT) AS $$ BEGIN SELECT fname, lname INTO first_name, last_name FROM users WHERE id = user_id; END; $$ LANGUAGE plpgsql;

💡 Hint

Check the placement of INTO and the RETURNS clause for OUT parameters.

Option D correctly declares OUT parameters and uses SELECT ... INTO to assign values. Option D uses RETURNS TABLE, not OUT parameters. Option D incorrectly uses RETURNS RECORD with OUT parameters. Option D has incorrect INTO placement causing syntax error.

🔧 Debug

advanced

Why does this function with OUT parameters raise an error?

Given this function:

CREATE FUNCTION get_product_info(p_id INT, OUT p_name TEXT, OUT p_price NUMERIC) AS $$
BEGIN
  SELECT name, price INTO p_name FROM products WHERE id = p_id;
END;
$$ LANGUAGE plpgsql;

Why does calling SELECT * FROM get_product_info(10); raise an error?

A Because the SELECT INTO only assigns p_name but not p_price, so p_price remains uninitialized causing an error.

B Because the function is missing a RETURN statement to return the OUT parameters.

C Because the function should use RETURNS TABLE instead of OUT parameters.

D Because the function is missing a semicolon after the SELECT statement.

💡 Hint

Check if all OUT parameters are assigned values before function ends.

The SELECT INTO only assigns p_name but not p_price. Since p_price is an OUT parameter, it must be assigned a value or the function will raise an error due to uninitialized output.

❓ optimization

expert

How to optimize a function with multiple OUT parameters to avoid multiple queries?

You have a function with three OUT parameters that fetch data from the same table. Which approach optimizes performance best?

A Use a single SELECT statement with SELECT ... INTO to assign all OUT parameters at once.

B Use three separate SELECT statements, each assigning one OUT parameter.

C Use RETURNS TABLE and perform three separate queries inside the function.

D Use OUT parameters but assign values using multiple UPDATE statements inside the function.

💡 Hint

Think about minimizing the number of queries to the database.

Using a single SELECT ... INTO statement to assign all OUT parameters at once reduces the number of queries and improves performance.