Function creation syntax in PostgreSQL - Step-by-Step Execution

Concept Flow - Function creation syntax

Start CREATE FUNCTION

↓

Define function name and parameters

↓

Specify return type

↓

Write function body

↓

End with LANGUAGE declaration

↓

Function created and stored

This flow shows the steps to create a function in PostgreSQL: start the command, define name and parameters, specify return type, write the body, declare language, then save.

Execution Sample

PostgreSQL

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

This code creates a function named add_numbers that takes two integers and returns their sum.

Execution Table

Step	Action	Details	Result
1	Start CREATE FUNCTION	Begin defining function add_numbers	Ready to define parameters
2	Define parameters	Parameters: a integer, b integer	Parameters set
3	Specify return type	RETURNS integer	Return type set
4	Write function body	BEGIN RETURN a + b; END;	Function logic defined
5	Declare language	LANGUAGE plpgsql	Language set to plpgsql
6	Execute statement	Create function in database	Function add_numbers created successfully

💡 Function creation completes after executing the full CREATE FUNCTION statement

Variable Tracker

Variable	Start	After Step 2	After Step 3	After Step 4	Final
Function Name	None	add_numbers	add_numbers	add_numbers	add_numbers
Parameters	None	(a integer, b integer)	(a integer, b integer)	(a integer, b integer)	(a integer, b integer)
Return Type	None	None	integer	integer	integer
Function Body	None	None	None	BEGIN RETURN a + b; END;	BEGIN RETURN a + b; END;
Language	None	None	None	None	plpgsql

Key Moments - 3 Insights

Why do we use $$ symbols around the function body?

The $$ symbols mark the start and end of the function body as a string literal, so PostgreSQL knows where the code block begins and ends (see Step 4 in execution_table).

What happens if we forget to specify the LANGUAGE?

PostgreSQL will give an error because it needs to know which language the function is written in to execute it (see Step 5 in execution_table).

Can the function have no parameters?

Yes, you can create functions without parameters by leaving the parentheses empty, but you still must define the return type and body.

Visual Quiz - 3 Questions

Look at the execution table, what is the return type set to at Step 3?

A plpgsql

B integer

C a + b

D add_numbers

💡 Hint

Check the 'Return Type' column in the variable_tracker after Step 3

At which step is the function body defined?

A Step 4

B Step 5

C Step 2

D Step 6

💡 Hint

Look at the 'Action' and 'Details' columns in the execution_table for where the body is written

If we remove the LANGUAGE declaration, what will happen?

A Function will return NULL

B Function will be created with default language

C PostgreSQL will throw an error

D Function will run but slower

💡 Hint

Refer to key_moments about the importance of LANGUAGE declaration

Concept Snapshot

CREATE FUNCTION function_name(parameters)
RETURNS return_type AS $$
BEGIN
  -- function body
END;
$$ LANGUAGE plpgsql;

- Use $$ to mark function body
- Specify parameters and return type
- Declare language (usually plpgsql)

Full Transcript

To create a function in PostgreSQL, start with CREATE FUNCTION, then name your function and list parameters with types. Next, specify the return type with RETURNS. Write the function body between $$ symbols using BEGIN and END. Finally, declare the language, commonly plpgsql. This process stores the function in the database for reuse.

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