RETURN and RETURN NEXT in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to return a single integer value from the function.

PostgreSQL

CREATE FUNCTION get_five() RETURNS integer AS $$ BEGIN RETURN [1]; END; $$ LANGUAGE plpgsql;

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

A'5'

B5

CRETURN 5

DSELECT 5

Attempts:

3 left

💡 Hint

Common Mistakes

Using quotes around numbers returns a string, not an integer.

Writing RETURN 5 inside RETURN causes syntax error.

The RETURN statement returns a single value from the function. Here, returning the integer 5 is correct.

2fill in blank

medium

Complete the code to return multiple rows from the function using RETURN NEXT.

PostgreSQL

CREATE FUNCTION get_numbers() RETURNS SETOF integer AS $$ BEGIN [1] 1; [1] 2; RETURN; END; $$ LANGUAGE plpgsql;

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

ASELECT

BRETURN

CRETURN NEXT

DRAISE NOTICE

Attempts:

3 left

💡 Hint

Common Mistakes

Using RETURN instead of RETURN NEXT for multiple rows.

Using SELECT inside the function without RETURN NEXT.

RETURN NEXT adds a row to the result set in a set-returning function. Multiple RETURN NEXT statements add multiple rows.

3fill in blank

hard

Fix the error in the function to correctly return multiple text rows.

PostgreSQL

CREATE FUNCTION get_words() RETURNS SETOF text AS $$ BEGIN [1] 'hello'; [1] 'world'; RETURN; END; $$ LANGUAGE plpgsql;

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

ARETURN NEXT

BRETURN

CRAISE NOTICE

DSELECT

Attempts:

3 left

💡 Hint

Common Mistakes

Using RETURN instead of RETURN NEXT for each row.

Using SELECT without RETURN NEXT.

To return multiple rows in a set-returning function, use RETURN NEXT for each row, then RETURN to finish.

4fill in blank

hard

Fill both blanks to return squares of numbers 1 to 3 using RETURN NEXT.

PostgreSQL

CREATE FUNCTION get_squares() RETURNS SETOF integer AS $$ DECLARE i integer := 1; BEGIN WHILE i <= 3 LOOP [1] i * i; i := i + 1; END LOOP; [2]; END; $$ LANGUAGE plpgsql;

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

ARETURN NEXT

BRETURN

CRAISE NOTICE

DSELECT

Attempts:

3 left

💡 Hint

Common Mistakes

Using RETURN inside the loop instead of RETURN NEXT.

Forgetting RETURN at the end of the function.

Inside the loop, use RETURN NEXT to add each square to the result set. After the loop, use RETURN to finish the function.

5fill in blank

hard

Fill all three blanks to return uppercase words longer than 3 letters using RETURN NEXT.

PostgreSQL

CREATE FUNCTION filter_words(words text[]) RETURNS SETOF text AS $$ DECLARE w text; BEGIN FOREACH w IN ARRAY words LOOP IF length(w) [1] 3 THEN [2] upper(w); END IF; END LOOP; [3]; END; $$ LANGUAGE plpgsql;

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

A>

BRETURN NEXT

CRETURN

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using < instead of > in the condition.

Using RETURN instead of RETURN NEXT inside the loop.

Forgetting RETURN at the end.

The condition checks if word length is greater than 3 using >. Use RETURN NEXT to add the uppercase word to the result set. Finally, RETURN ends the function and returns all rows.