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.