Functions returning SETOF in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to declare a function that returns a set of integers.

PostgreSQL

CREATE FUNCTION get_numbers() RETURNS [1] AS $$ BEGIN RETURN QUERY SELECT generate_series(1, 5); END; $$ LANGUAGE plpgsql;

Drag options to blanks, or click blank then click option'

ATABLE

Binteger

CSETOF integer

DSETOF text

Attempts:

3 left

💡 Hint

Common Mistakes

Using just 'integer' returns a single value, not a set.

Using 'TABLE' without specifying columns is incorrect here.

The function must return a set of integers, so RETURNS SETOF integer is correct.

2fill in blank

medium

Complete the code to return rows from the function using RETURN QUERY.

PostgreSQL

CREATE FUNCTION get_names() RETURNS SETOF text AS $$ BEGIN RETURN QUERY SELECT [1] FROM unnest(array['Alice', 'Bob', 'Carol']) AS names(name); END; $$ LANGUAGE plpgsql;

Drag options to blanks, or click blank then click option'

Aname

Btext

Cnames

Dunnest

Attempts:

3 left

💡 Hint

Common Mistakes

Selecting 'names' which is the table alias, not the column.

Selecting 'text' which is a data type, not a column.

The alias for the unnest array is names(name), so selecting name returns the text values.

3fill in blank

hard

Fix the error in the function to correctly return a set of rows from a table.

PostgreSQL

CREATE FUNCTION get_employees() RETURNS SETOF employees AS $$ BEGIN RETURN QUERY SELECT * FROM [1]; END; $$ LANGUAGE plpgsql;

Drag options to blanks, or click blank then click option'

Aemployees

Bemp

Cemployee_table

Demployee

Attempts:

3 left

💡 Hint

Common Mistakes

Using a singular table name that does not exist.

Using a different table name that does not match the return type.

The function returns SETOF employees, so the table name must be employees to match the type.

4fill in blank

hard

Fill both blanks to create a function returning a set of rows with a WHERE condition.

PostgreSQL

CREATE FUNCTION get_active_users() RETURNS SETOF users AS $$ BEGIN RETURN QUERY SELECT * FROM users WHERE status = [1]; END; $$ LANGUAGE [2];

Drag options to blanks, or click blank then click option'

A'active'

B'inactive'

Cplpgsql

Dsql

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'inactive' instead of 'active' for the status.

Using 'sql' language which does not support BEGIN-END blocks.

The function filters users with status 'active' and uses the plpgsql language.

5fill in blank

hard

Fill all three blanks to create a function returning a filtered set of rows with a parameter.

PostgreSQL

CREATE FUNCTION get_orders_by_customer([1] integer) RETURNS SETOF orders AS $$ BEGIN RETURN QUERY SELECT * FROM orders WHERE customer_id = [2]; END; $$ LANGUAGE [3];

Drag options to blanks, or click blank then click option'

Acust_id

Cplpgsql

Dsql

Attempts:

3 left

💡 Hint

Common Mistakes

Using different names for parameter and WHERE clause variable.

Using 'sql' language which does not support BEGIN-END blocks.

The parameter name is cust_id, used in the WHERE clause, and the function language is plpgsql.

Practice

1.

What does a PostgreSQL function declared with RETURNS SETOF do?

easy

A. It returns multiple rows as a set of values.

B. It returns a single scalar value.

C. It returns a boolean indicating success or failure.

D. It returns a JSON object.

Solution

1. Step 1: Understand the meaning of RETURNS SETOF

   In PostgreSQL, RETURNS SETOF means the function returns multiple rows, not just one value.

2. Step 2: Compare with other return types

   Other return types like scalar or boolean return single values, not sets of rows.

3. Final Answer:

   It returns multiple rows as a set of values. -> Option A

4. Quick Check:

   RETURNS SETOF = multiple rows [OK]

Hint: SETOF means multiple rows, not one value [OK]

Common Mistakes:

• Thinking it returns a single value
• Confusing SETOF with JSON return
• Assuming it returns a boolean

2.

Which of the following is the correct syntax to declare a PostgreSQL function that returns a set of integers?

CREATE FUNCTION get_numbers() RETURNS SETOF integer AS $$ BEGIN RETURN QUERY SELECT ...; END; $$ LANGUAGE plpgsql;

easy

A. CREATE FUNCTION get_numbers() RETURNS SETOF integer AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION get_numbers() RETURNS integer AS $$ BEGIN RETURN QUERY SELECT 1; END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION get_numbers() RETURNS SETOF integer AS $$ BEGIN RETURN QUERY SELECT 1; END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION get_numbers() RETURNS TABLE(integer) AS $$ BEGIN RETURN QUERY SELECT 1; END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Check the correct RETURNS clause

   To return multiple rows of integers, use RETURNS SETOF integer.

2. Step 2: Use RETURN QUERY for sets

   Inside the function, RETURN QUERY SELECT ... returns multiple rows properly.

3. Final Answer:

   CREATE FUNCTION get_numbers() RETURNS SETOF integer AS $$ BEGIN RETURN QUERY SELECT 1; END; $$ LANGUAGE plpgsql; -> Option C

4. Quick Check:

   RETURNS SETOF + RETURN QUERY = CREATE FUNCTION get_numbers() RETURNS SETOF integer AS $$ BEGIN RETURN QUERY SELECT 1; END; $$ LANGUAGE plpgsql; [OK]

Hint: Use RETURNS SETOF and RETURN QUERY for multiple rows [OK]

Common Mistakes:

• Using RETURNS integer instead of SETOF integer
• Using RETURN instead of RETURN QUERY for sets
• Confusing RETURNS TABLE with RETURNS SETOF

3.

Given this function:

CREATE FUNCTION get_even_numbers() RETURNS SETOF integer AS $$ BEGIN RETURN QUERY SELECT generate_series(1,5) WHERE generate_series % 2 = 0; END; $$ LANGUAGE plpgsql;

What will SELECT * FROM get_even_numbers(); return?

medium

A. Empty set

B. [1, 3, 5]

C. [1, 2, 3, 4, 5]

D. [2, 4]

Solution

1. Step 1: Understand generate_series and filter

   The function selects numbers from 1 to 5 but filters only even numbers using WHERE generate_series % 2 = 0.

2. Step 2: Identify even numbers in range

   Even numbers between 1 and 5 are 2 and 4.

3. Final Answer:

   [2, 4] -> Option D

4. Quick Check:

   Even numbers 1-5 = [2,4] [OK]

Hint: Filter generate_series with modulo for evens [OK]

Common Mistakes:

• Including odd numbers by mistake
• Returning all numbers without filter
• Expecting empty set due to syntax confusion

4.

Identify the error in this function that returns a set of text values:

CREATE FUNCTION get_names() RETURNS SETOF text AS $$ BEGIN RETURN SELECT name FROM users; END; $$ LANGUAGE plpgsql;

medium

A. Missing RETURN QUERY before SELECT statement.

B. RETURNS SETOF text is invalid syntax.

C. Function must return TABLE, not SETOF.

D. LANGUAGE plpgsql is not allowed for set-returning functions.

Solution

1. Step 1: Check how to return multiple rows in plpgsql

   To return multiple rows, use RETURN QUERY SELECT ... inside the function.

2. Step 2: Identify missing keyword

   The function uses RETURN SELECT ... which is invalid; it must be RETURN QUERY SELECT ....

3. Final Answer:

   Missing RETURN QUERY before SELECT statement. -> Option A

4. Quick Check:

   Use RETURN QUERY for sets [OK]

Hint: Use RETURN QUERY to return sets inside plpgsql [OK]

Common Mistakes:

• Using RETURN instead of RETURN QUERY
• Confusing RETURNS SETOF with RETURNS TABLE
• Thinking LANGUAGE plpgsql disallows set returns

5.

You want to create a function that returns all employees with salary above a given amount. Which is the best way to write this function?

CREATE FUNCTION get_high_salary(min_salary numeric) RETURNS SETOF employees AS $$ BEGIN RETURN QUERY SELECT * FROM employees WHERE salary > min_salary; END; $$ LANGUAGE plpgsql;

What is the correct way to call this function to get all employees earning more than 50000?

hard

A. SELECT get_high_salary(50000);

B. SELECT * FROM get_high_salary(50000);

C. CALL get_high_salary(50000);

D. EXECUTE get_high_salary(50000);

Solution

1. Step 1: Understand function returns SETOF employees

   The function returns multiple rows, so it must be called in a FROM clause to get rows.

2. Step 2: Choose correct call syntax

   Use SELECT * FROM function_name(args); to get all rows returned by the function.

3. Final Answer:

   SELECT * FROM get_high_salary(50000); -> Option B

4. Quick Check:

   Call set-returning function in FROM clause [OK]

Hint: Call set-returning functions with SELECT * FROM [OK]

Common Mistakes:

• Using SELECT function() without FROM
• Using CALL which is for procedures
• Using EXECUTE which is for dynamic SQL