Postgres functions basics in Supabase - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ service_behavior

intermediate

What is the output of this Postgres function call?

Consider the following Postgres function that returns the sum of two integers. What will be the result of calling SELECT add_numbers(3, 5);?

Supabase

CREATE OR REPLACE FUNCTION add_numbers(a integer, b integer) RETURNS integer AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

A 35

B NULL

C ERROR: function does not exist

D 8

💡 Hint

Think about what the function does with the two input numbers.

The function adds the two integer inputs and returns their sum. So 3 + 5 equals 8.

🧠 Conceptual

intermediate

What error occurs when calling a Postgres function with wrong argument types?

Given a Postgres function defined as CREATE FUNCTION greet(name text) RETURNS text, what error will occur if you call SELECT greet(123);?

Supabase

CREATE OR REPLACE FUNCTION greet(name text) RETURNS text AS $$
BEGIN
  RETURN 'Hello, ' || name;
END;
$$ LANGUAGE plpgsql;

A ERROR: syntax error near 123

B ERROR: function greet(integer) does not exist

C NULL

D Hello, 123

💡 Hint

Check the argument type expected versus the type provided.

Postgres functions are strict about argument types. Passing an integer where text is expected causes a 'function does not exist' error.

❓ Architecture

advanced

Which option correctly creates a Postgres function that returns a table?

You want to create a Postgres function that returns a table with columns id integer and name text. Which option correctly defines this function?

A CREATE FUNCTION get_users() RETURNS TABLE(id integer, name text) AS $$ BEGIN RETURN QUERY SELECT id, name FROM users; END; $$ LANGUAGE plpgsql;

B CREATE FUNCTION get_users() RETURNS SETOF RECORD AS $$ BEGIN RETURN SELECT id, name FROM users; END; $$ LANGUAGE plpgsql;

C CREATE FUNCTION get_users() RETURNS TABLE(id integer, name text) AS $$ BEGIN RETURN SELECT id, name FROM users; END; $$ LANGUAGE plpgsql;

D CREATE FUNCTION get_users() RETURNS SETOF users AS $$ BEGIN RETURN QUERY SELECT * FROM users; END; $$ LANGUAGE plpgsql;

💡 Hint

Look for the correct use of RETURN QUERY and RETURNS TABLE syntax.

Option A correctly uses RETURNS TABLE and RETURN QUERY to return a set of rows with specified columns.

❓ security

advanced

Which option correctly restricts a Postgres function to run with the caller's permissions?

You want a Postgres function to execute with the permissions of the user calling it, not the function owner. Which option achieves this?

A CREATE FUNCTION secure_func() RETURNS void SECURITY INVOKER AS $$ BEGIN -- code END; $$ LANGUAGE plpgsql;

B CREATE FUNCTION secure_func() RETURNS void WITH SECURITY AS $$ BEGIN -- code END; $$ LANGUAGE plpgsql;

C CREATE FUNCTION secure_func() RETURNS void AS $$ BEGIN -- code END; $$ LANGUAGE plpgsql;

D CREATE FUNCTION secure_func() RETURNS void SECURITY DEFINER AS $$ BEGIN -- code END; $$ LANGUAGE plpgsql;

💡 Hint

Check the SECURITY clause that controls execution rights.

SECURITY INVOKER makes the function run with the caller's rights, while SECURITY DEFINER runs with the function owner's rights.

✅ Best Practice

expert

What is the best practice to handle exceptions inside a Postgres function?

You want to catch errors inside a Postgres function and return a custom message instead of failing. Which code snippet correctly implements this?

A

BEGIN
  -- code
ON ERROR RETURN 'Error occurred';
END;

B

BEGIN
  -- code
CATCH ALL THEN
  RETURN 'Error occurred';
END;

C

BEGIN
  -- code
EXCEPTION WHEN OTHERS THEN
  RETURN 'Error occurred';
END;

D

BEGIN
  -- code
TRY
  RETURN 'Success';
CATCH
  RETURN 'Error occurred';
END TRY;

💡 Hint

Postgres uses EXCEPTION blocks for error handling in PL/pgSQL.

Option C uses the correct EXCEPTION WHEN OTHERS THEN syntax to catch all exceptions and handle them.

Practice

1. What is the main purpose of a Postgres function in Supabase?

easy

A. To store reusable code inside the database

B. To create user interface elements

C. To manage server hardware

D. To backup the entire database automatically

Solution

1. Step 1: Understand what Postgres functions do

   Postgres functions hold code that can be run repeatedly inside the database.

2. Step 2: Compare options with this purpose

   Only To store reusable code inside the database matches this description; others relate to UI, hardware, or backups, which are unrelated.

3. Final Answer:

   To store reusable code inside the database -> Option A

4. Quick Check:

   Postgres functions = reusable database code [OK]

Hint: Functions store code to reuse inside the database [OK]

Common Mistakes:

• Confusing functions with UI components
• Thinking functions manage hardware
• Assuming functions handle backups automatically

2. Which of the following is the correct way to start defining a simple Postgres function?

easy

A. FUNCTION CREATE function_name() RETURNS type AS BEGIN ... END;

B. MAKE FUNCTION function_name RETURNS type { ... }

C. CREATE FUNCTION function_name() RETURNS type AS $$ BEGIN ... END; $$ LANGUAGE plpgsql;

D. DEFINE FUNCTION function_name() RETURNS type BEGIN ... END;

Solution

1. Step 1: Recall Postgres function syntax

   The correct syntax starts with CREATE FUNCTION, includes RETURNS, and uses $$ to wrap the body.

2. Step 2: Match options to syntax

   Only CREATE FUNCTION function_name() RETURNS type AS $$ BEGIN ... END; $$ LANGUAGE plpgsql; matches the exact syntax; others use incorrect keywords or order.

3. Final Answer:

   CREATE FUNCTION function_name() RETURNS type AS $$ BEGIN ... END; $$ LANGUAGE plpgsql; -> Option C

4. Quick Check:

   CREATE FUNCTION + RETURNS + $$ ... $$ = correct syntax [OK]

Hint: Postgres functions start with CREATE FUNCTION and use $$ for body [OK]

Common Mistakes:

• Using MAKE or DEFINE instead of CREATE
• Omitting LANGUAGE plpgsql
• Not wrapping function body with $$

3. Given this function, what will SELECT add_two_numbers(3, 4); return?

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

medium

A. NULL

B. 34

C. Error: function not found

D. 7

Solution

1. Step 1: Understand function behavior

   The function adds two integers a and b and returns their sum.

2. Step 2: Calculate the result for inputs 3 and 4

   3 + 4 equals 7, so the function returns 7.

3. Final Answer:

   7 -> Option D

4. Quick Check:

   3 + 4 = 7 [OK]

Hint: Function adds inputs; 3 + 4 = 7 [OK]

Common Mistakes:

• Concatenating numbers as strings (34)
• Assuming function is missing or returns NULL
• Confusing syntax errors with output

4. Identify the error in this Postgres function:

CREATE FUNCTION multiply_by_two(x integer) RETURNS integer AS $$ BEGIN RETURN x * 2 END; $$ LANGUAGE plpgsql;

medium

A. Missing semicolon after RETURN statement

B. Incorrect function name

C. Wrong language specified

D. No error, function is correct

Solution

1. Step 1: Check function syntax carefully

   In PL/pgSQL, statements inside BEGIN...END must end with semicolons.

2. Step 2: Locate missing semicolon

   The RETURN statement lacks a semicolon at the end, causing a syntax error.

3. Final Answer:

   Missing semicolon after RETURN statement -> Option A

4. Quick Check:

   Statements inside BEGIN...END need semicolons [OK]

Hint: Check for missing semicolons inside BEGIN...END [OK]

Common Mistakes:

• Forgetting semicolon after RETURN
• Assuming function name or language is wrong
• Thinking function is correct without semicolon

5. You want to create a Postgres function that returns the length of a text input only if it is not empty; otherwise, it returns NULL. Which function definition correctly implements this?

hard

A. CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN RETURN length(input); END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN IF input = '' THEN RETURN NULL; ELSE RETURN length(input); END IF; END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN IF input IS NULL THEN RETURN 0; ELSE RETURN length(input); END IF; END; $$ LANGUAGE plpgsql;

D. CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN IF input = NULL THEN RETURN NULL; ELSE RETURN length(input); END IF; END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Understand the condition to check empty string

   The function must check if input equals empty string ('') and return NULL in that case.

2. Step 2: Evaluate each option's condition

   CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN IF input = '' THEN RETURN NULL; ELSE RETURN length(input); END IF; END; $$ LANGUAGE plpgsql; correctly checks input = '' and returns NULL; CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN RETURN length(input); END; $$ LANGUAGE plpgsql; always returns length; CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN IF input IS NULL THEN RETURN 0; ELSE RETURN length(input); END IF; END; $$ LANGUAGE plpgsql; checks NULL instead of empty; CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN IF input = NULL THEN RETURN NULL; ELSE RETURN length(input); END IF; END; $$ LANGUAGE plpgsql; uses incorrect NULL comparison.

3. Final Answer:

   CREATE FUNCTION text_length_if_not_empty(input text) RETURNS integer AS $$ BEGIN IF input = '' THEN RETURN NULL; ELSE RETURN length(input); END IF; END; $$ LANGUAGE plpgsql; -> Option B

4. Quick Check:

   Check empty string with = '' and return NULL [OK]

Hint: Use IF input = '' THEN RETURN NULL; ELSE RETURN length(input); [OK]

Common Mistakes:

• Using input = NULL instead of IS NULL
• Not checking for empty string
• Returning 0 instead of NULL for empty input