Functions returning TABLE in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to declare a function that returns a table.

PostgreSQL

CREATE FUNCTION get_users() RETURNS [1] AS $$ BEGIN RETURN QUERY SELECT id, name FROM users; END; $$ LANGUAGE plpgsql;

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

AINT

BSETOF users

CTEXT

DTABLE(id INT, name TEXT)

Attempts:

3 left

💡 Hint

Common Mistakes

Using RETURNS INT or RETURNS TEXT instead of RETURNS TABLE.

Using SETOF users when the function specifies columns explicitly.

The function returns a table with columns id and name. So RETURNS TABLE(id INT, name TEXT) is correct.

2fill in blank

medium

Complete the code to return rows from the function.

PostgreSQL

CREATE FUNCTION get_active_users() RETURNS TABLE(id INT, name TEXT) AS $$ BEGIN RETURN [1] SELECT id, name FROM users WHERE active = true; END; $$ LANGUAGE plpgsql;

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

ARETURN NEXT

BRETURN QUERY

CRETURN ROW

DRETURN TABLE

Attempts:

3 left

💡 Hint

Common Mistakes

Using RETURN NEXT without a loop to return multiple rows.

Using RETURN ROW which is for single row returns.

In PL/pgSQL, RETURN QUERY is used to return the result of a query from a function returning TABLE.

3fill in blank

hard

Fix the error in the function that returns a table.

PostgreSQL

CREATE FUNCTION get_scores() RETURNS TABLE(player TEXT, score INT) AS $$ BEGIN [1] SELECT player, score FROM scores; END; $$ LANGUAGE plpgsql;

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

ARETURN QUERY

BRETURN

CRETURN NEXT

DRETURN ROW

Attempts:

3 left

💡 Hint

Common Mistakes

Using RETURN without QUERY causes syntax error.

Using RETURN NEXT without looping over rows.

The function must use RETURN QUERY to return multiple rows from the SELECT statement.

4fill in blank

hard

Fill both blanks to define a function returning a table with two columns and returning rows.

PostgreSQL

CREATE FUNCTION get_products() RETURNS [1] AS $$ BEGIN [2] SELECT id, name FROM products; END; $$ LANGUAGE plpgsql;

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

ATABLE(id INT, name TEXT)

BRETURN QUERY

CSETOF products

DRETURN NEXT

Attempts:

3 left

💡 Hint

Common Mistakes

Using SETOF products with RETURNS TABLE syntax.

Using RETURN NEXT without a loop.

The function returns a table with columns, so use RETURNS TABLE(id INT, name TEXT). To return rows, use RETURN QUERY.

5fill in blank

hard

Fill all three blanks to create a function returning a table with columns, returning rows, and specifying language.

PostgreSQL

CREATE FUNCTION get_orders() RETURNS [1] AS $$ BEGIN [2] SELECT order_id, total FROM orders; END; $$ LANGUAGE [3];

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

ATABLE(order_id INT, total NUMERIC)

BRETURN QUERY

Cplpgsql

DSETOF orders

Attempts:

3 left

💡 Hint

Common Mistakes

Using SETOF orders with RETURNS TABLE syntax.

Forgetting to specify LANGUAGE plpgsql.

The function returns a table with columns, so use RETURNS TABLE(order_id INT, total NUMERIC). Use RETURN QUERY to return rows. The language is plpgsql.