Function creation syntax in PostgreSQL - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to start creating a function named 'add_numbers'.

PostgreSQL

CREATE FUNCTION [1](a integer, b integer) RETURNS integer AS $$ BEGIN RETURN a + b; END; $$ LANGUAGE plpgsql;

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

Asum_numbers

Badd_numbers

Ccalculate_sum

Dadd_two

Attempts:

3 left

💡 Hint

Common Mistakes

Using a different function name than 'add_numbers'.

Forgetting to write the function name after CREATE FUNCTION.

The function name is defined right after CREATE FUNCTION. Here, it should be add_numbers as requested.

2fill in blank

medium

Complete the code to specify the language used for the function.

PostgreSQL

CREATE FUNCTION add_numbers(a integer, b integer) RETURNS integer AS $$ BEGIN RETURN a + b; END; $$ LANGUAGE [1];

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

Asql

Bpython

Cplsql

Dplpgsql

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'sql' instead of 'plpgsql'.

Using 'plsql' which is Oracle's language, not PostgreSQL's.

PostgreSQL procedural language for functions is plpgsql. This must be specified after the LANGUAGE keyword.

3fill in blank

hard

Fix the error in the function header by completing the RETURNS clause correctly.

PostgreSQL

CREATE FUNCTION multiply_numbers(a integer, b integer) RETURNS [1] AS $$ BEGIN RETURN a * b; END; $$ LANGUAGE plpgsql;

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

Ainteger

Btext

Cvoid

Dboolean

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'text' or 'boolean' as return type which does not match the returned value.

Using 'void' which means no return value.

The function returns the product of two integers, so the return type must be integer.

4fill in blank

hard

Fill both blanks to complete the function that returns the length of a text input.

PostgreSQL

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

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

Alength

Binput_text

Clen

Dtext

Attempts:

3 left

💡 Hint

Common Mistakes

Using 'len' which is Python, not PostgreSQL.

Using 'text' which is a type, not a function.

Using wrong variable name.

The PostgreSQL function to get string length is length, and it takes the input text variable input_text as argument.

5fill in blank

hard

Fill all three blanks to create a function that returns TRUE if a number is positive.

PostgreSQL

CREATE FUNCTION is_positive(num integer) RETURNS boolean AS $$ BEGIN IF num [1] 0 THEN RETURN [2]; ELSE RETURN [3]; END IF; END; $$ LANGUAGE plpgsql;

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

A>

BTRUE

CFALSE

D<

Attempts:

3 left

💡 Hint

Common Mistakes

Using '<' instead of '>' for positive check.

Returning TRUE and FALSE in wrong order.

The function checks if num is greater than zero, returning TRUE if yes, otherwise FALSE.