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.