Function creation syntax in PostgreSQL

Introduction

Functions let you save a set of instructions in the database to reuse later. This helps avoid repeating the same code.

You want to calculate a value many times without rewriting the calculation.

You need to organize complex logic inside the database for easier use.

You want to perform a task that involves multiple steps and reuse it.

You want to simplify queries by wrapping repeated code inside a function.

You want to improve performance by running code directly in the database.

Syntax

PostgreSQL

CREATE [OR REPLACE] FUNCTION function_name (parameters) 
RETURNS return_type AS $$
BEGIN
  -- function body
  RETURN value;
END;
$$ LANGUAGE plpgsql;

CREATE OR REPLACE lets you update the function if it exists.

The function body goes between BEGIN and END;.

Examples

This function adds two numbers and returns the sum.

PostgreSQL

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

This function returns a greeting message with the given name.

PostgreSQL

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

This function returns the current year as an integer.

PostgreSQL

CREATE FUNCTION get_current_year()
RETURNS integer AS $$
BEGIN
  RETURN EXTRACT(YEAR FROM CURRENT_DATE)::integer;
END;
$$ LANGUAGE plpgsql;

Sample Program

This creates a function that multiplies a number by three, then calls it with 5.

PostgreSQL

CREATE OR REPLACE FUNCTION multiply_by_three(num integer)
RETURNS integer AS $$
BEGIN
  RETURN num * 3;
END;
$$ LANGUAGE plpgsql;

SELECT multiply_by_three(5);

Important Notes

Always specify the language (usually plpgsql) at the end.

Use CREATE OR REPLACE to update functions without dropping them first.

Functions can take zero or more parameters and must specify a return type.

Summary

Functions store reusable code inside the database.

Use CREATE FUNCTION with parameters, return type, and body.

Call functions like normal queries to get results.

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