Function creation syntax in PostgreSQL - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ query_result

intermediate

Output of a simple PostgreSQL function

What is the output of this PostgreSQL function call?

CREATE OR REPLACE FUNCTION add_five(x integer) RETURNS integer AS $$
BEGIN
  RETURN x + 5;
END;
$$ LANGUAGE plpgsql;

SELECT add_five(10);

PostgreSQL

CREATE OR REPLACE FUNCTION add_five(x integer) RETURNS integer AS $$
BEGIN
  RETURN x + 5;
END;
$$ LANGUAGE plpgsql;

SELECT add_five(10);

A 15

B Error: function does not exist

C 5

D 10

💡 Hint

The function adds 5 to the input number.

The function add_five takes an integer input and returns the input plus 5. Calling add_five(10) returns 15.

📝 Syntax

intermediate

Identify the syntax error in function creation

Which option contains a syntax error in the PostgreSQL function definition?

A CREATE FUNCTION subtract(x integer, y integer) RETURNS integer AS $$ BEGIN RETURN x - y; END; $$ LANGUAGE plpgsql;

B CREATE FUNCTION add(x integer, y integer) RETURNS integer AS $$ BEGIN RETURN x + y; END; $$ LANGUAGE plpgsql;

C CREATE FUNCTION greet() RETURNS text AS $$ BEGIN RETURN 'Hello'; END $$ LANGUAGE plpgsql;

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

💡 Hint

Check for missing semicolons inside the function body.

Option B is missing a semicolon after RETURN x + y, which causes a syntax error.

❓ optimization

advanced

Choose the most efficient function for returning a constant value

Which function definition is the most efficient way to return the constant integer 42 in PostgreSQL?

A CREATE FUNCTION get_constant() RETURNS integer AS $$ BEGIN RETURN 40 + 2; END; $$ LANGUAGE plpgsql;

B CREATE FUNCTION get_constant() RETURNS integer AS $$ BEGIN RETURN 42; END; $$ LANGUAGE plpgsql;

C CREATE FUNCTION get_constant() RETURNS integer AS $$ BEGIN RETURN CAST('42' AS integer); END; $$ LANGUAGE plpgsql;

D CREATE FUNCTION get_constant() RETURNS integer AS $$ SELECT 42; $$ LANGUAGE sql;

💡 Hint

SQL language functions are simpler and faster for single expressions.

Option D uses LANGUAGE sql with a simple SELECT statement, which is more efficient than plpgsql blocks for returning constants.

🧠 Conceptual

advanced

Understanding function volatility categories

Which volatility category should you assign to a PostgreSQL function that always returns the current timestamp?

A VOLATILE

B IMMUTABLE

C STABLE

D CONSTANT

💡 Hint

The function returns different results even with the same inputs.

Functions returning current timestamp are VOLATILE because their output can change on each call.

🔧 Debug

expert

Diagnose the error in this function creation

What error will this PostgreSQL function raise when created?

CREATE FUNCTION faulty_func(x integer) RETURNS integer AS $$
BEGIN
  IF x > 0 THEN
    RETURN x;
  ELSE
    RETURN;
  END IF;
END;
$$ LANGUAGE plpgsql;

PostgreSQL

CREATE FUNCTION faulty_func(x integer) RETURNS integer AS $$
BEGIN
  IF x > 0 THEN
    RETURN x;
  ELSE
    RETURN;
  END IF;
END;
$$ LANGUAGE plpgsql;

A Syntax error: missing semicolon

B Runtime error: function returns NULL without explicit return type

C Error: RETURN statement with no value in function returning integer

D No error, function runs correctly

💡 Hint

Check the RETURN statements and their values.

The ELSE branch has RETURN with no value, but the function must return an integer, causing an error.

Practice

1. What is the purpose of the CREATE FUNCTION statement in PostgreSQL?

easy

A. To delete rows from a table

B. To create a new table in the database

C. To insert data into an existing table

D. To define a reusable block of code that can be called later

Solution

1. Step 1: Understand the role of functions in PostgreSQL

   Functions store reusable code inside the database to perform tasks repeatedly.

2. Step 2: Identify what CREATE FUNCTION does

   This statement defines a new function with parameters, return type, and body.

3. Final Answer:

   To define a reusable block of code that can be called later -> Option D

4. Quick Check:

   CREATE FUNCTION defines reusable code [OK]

Hint: Functions store reusable code blocks in the database [OK]

Common Mistakes:

• Confusing function creation with table creation
• Thinking it inserts or deletes data directly
• Mixing up functions with SQL commands like SELECT or DELETE

2. Which of the following is the correct basic syntax to create a function in PostgreSQL that returns an integer?

easy

A. CREATE FUNCTION myfunc() RETURNS int AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION myfunc RETURNS int AS $$ RETURN 1; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION myfunc() RETURNS integer BEGIN RETURN 1; END LANGUAGE plpgsql;

D. CREATE FUNCTION myfunc() RETURNS int AS BEGIN RETURN 1; END LANGUAGE plpgsql;

Solution

1. Step 1: Check the correct syntax for function creation

   The syntax requires parentheses after the function name, the RETURNS clause, the function body enclosed in $$, and the LANGUAGE specified.

2. Step 2: Validate each option

   CREATE FUNCTION myfunc() RETURNS int AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql; correctly uses parentheses, RETURNS int, AS $$ ... $$, and LANGUAGE plpgsql. Others miss parentheses, AS $$, or semicolons.

3. Final Answer:

   CREATE FUNCTION myfunc() RETURNS int AS $$ BEGIN RETURN 1; END; $$ LANGUAGE plpgsql; -> Option A

4. Quick Check:

   Correct syntax includes parentheses, RETURNS, AS $$, LANGUAGE [OK]

Hint: Always use () after function name and AS $$ for body [OK]

Common Mistakes:

• Omitting parentheses after function name
• Missing AS $$ ... $$ around function body
• Not specifying LANGUAGE plpgsql
• Forgetting semicolons inside function body

3. Given the function below, what will be the output of SELECT add_one(5);?

CREATE FUNCTION add_one(x integer) RETURNS integer AS $$ BEGIN RETURN x + 1; END; $$ LANGUAGE plpgsql;

medium

A. 6

B. Syntax error

C. 5

D. NULL

Solution

1. Step 1: Understand the function logic

   The function takes an integer input x and returns x + 1.

2. Step 2: Apply the input value 5

   Calling add_one(5) returns 5 + 1 = 6.

3. Final Answer:

   6 -> Option A

4. Quick Check:

   Input 5 plus 1 equals 6 [OK]

Hint: Function adds 1 to input, so 5 becomes 6 [OK]

Common Mistakes:

• Confusing input and output values
• Expecting syntax error due to unfamiliarity
• Assuming function returns NULL without reason

4. Identify the error in the following function definition:

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

medium

A. Missing RETURNS clause

B. Incorrect function name syntax

C. Missing semicolon after RETURN statement

D. LANGUAGE plpgsql is not allowed

Solution

1. Step 1: Review function body syntax

   In PL/pgSQL, each statement inside the function body must end with a semicolon.

2. Step 2: Locate missing semicolon

   The RETURN statement lacks a semicolon after x * 2, causing a syntax error.

3. Final Answer:

   Missing semicolon after RETURN statement -> Option C

4. Quick Check:

   Statements inside function need semicolons [OK]

Hint: Check for semicolons after each statement inside function [OK]

Common Mistakes:

• Forgetting semicolon after RETURN
• Misplacing LANGUAGE clause
• Omitting RETURNS clause
• Using invalid function names

5. You want to create a PostgreSQL function concat_names that takes two text parameters and returns their concatenation separated by a space. Which of the following is the correct function definition?

hard

A. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ RETURN a + ' ' + b; $$ LANGUAGE plpgsql;

B. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN a || ' ' || b; END; $$ LANGUAGE plpgsql;

C. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN concat(a, ' ', b); END $$ LANGUAGE plpgsql;

D. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN a & ' ' & b; END; $$ LANGUAGE plpgsql;

Solution

1. Step 1: Understand string concatenation in PostgreSQL

   PostgreSQL uses the || operator to concatenate strings.

2. Step 2: Evaluate each option's concatenation method

   CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN a || ' ' || b; END; $$ LANGUAGE plpgsql; uses || correctly with BEGIN...END and semicolons. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ RETURN a + ' ' + b; $$ LANGUAGE plpgsql; uses + which is invalid for text. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN concat(a, ' ', b); END $$ LANGUAGE plpgsql; misses semicolon after END. CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN a & ' ' & b; END; $$ LANGUAGE plpgsql; uses & which is invalid.

3. Final Answer:

   CREATE FUNCTION concat_names(a text, b text) RETURNS text AS $$ BEGIN RETURN a || ' ' || b; END; $$ LANGUAGE plpgsql; -> Option B

4. Quick Check:

   Use || for text concatenation in PL/pgSQL [OK]

Hint: Use || operator for text concatenation in PostgreSQL functions [OK]

Common Mistakes:

• Using + or & instead of || for strings
• Forgetting semicolon after END
• Missing BEGIN...END block for multiple statements