Why utility functions matter in PostgreSQL - Test Your Understanding

Practice - 5 Tasks

1fill in blank

easy

Complete the code to create a simple utility function that returns the square of a number.

PostgreSQL

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

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

A+

B*

C-

D/

Attempts:

3 left

💡 Hint

Common Mistakes

Using addition instead of multiplication.

Using division or subtraction which do not calculate squares.

The multiplication operator * is used to calculate the square of the number by multiplying it by itself.

2fill in blank

medium

Complete the code to call the utility function 'square' for the number 5.

PostgreSQL

SELECT [1](5);

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

Apower

Bsqrt

Csquare

Dabs

Attempts:

3 left

💡 Hint

Common Mistakes

Using built-in functions like power or sqrt instead of the custom function.

Misspelling the function name.

The function square is the custom utility function we created to calculate the square of a number.

3fill in blank

hard

Fix the error in the function by completing the missing keyword to define the function body.

PostgreSQL

CREATE FUNCTION greet(name text) RETURNS text AS $$ [1] RETURN 'Hello, ' || name || '!'; END; $$ LANGUAGE plpgsql;

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

AFUNCTION

BDECLARE

CEND

DBEGIN

Attempts:

3 left

💡 Hint

Common Mistakes

Using DECLARE instead of BEGIN to start the body.

Omitting the BEGIN keyword.

The BEGIN keyword starts the function body in PL/pgSQL.

4fill in blank

hard

Fill both blanks to create a utility function that returns the length of a given text.

PostgreSQL

CREATE FUNCTION text_length(input_text text) RETURNS integer AS $$ [1] RETURN [2](input_text); END; $$ LANGUAGE plpgsql;

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

ABEGIN

BLENGTH

CLEN

DDECLARE

Attempts:

3 left

💡 Hint

Common Mistakes

Using DECLARE instead of BEGIN to start the body.

Using LEN which is not a PostgreSQL function.

The function body starts with BEGIN and uses the built-in LENGTH function to get the text length.

5fill in blank

hard

Fill all three blanks to create a utility function that returns the uppercase version of a text input.

PostgreSQL

CREATE FUNCTION to_uppercase(text_input text) RETURNS text AS $$ [1] RETURN [2]([3]); END; $$ LANGUAGE plpgsql;

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

ABEGIN

BUPPER

Ctext_input

DDECLARE

Attempts:

3 left

💡 Hint

Common Mistakes

Starting with DECLARE instead of BEGIN.

Using wrong function names like LOWER or misspelling UPPER.

Using a wrong variable name instead of text_input.

The function body starts with BEGIN, uses the UPPER function, and applies it to the input parameter text_input.