SQL Functions

What is a Function?

• A Function is a database object that performs a specific tack. It accepts input values, processes them, and returns a single value or a table.

Why Do We Need a Function?

Suppose you want to calculate the Total Price many times.

Instead of writing:

SELECT Quantity * Price
FROM Product;

again and again, you can create a Function once and reuse it.

How Does a Function Work?

Input
  |
Function
  |
Process
  |
Output

Syntax: (PostgreSQL)

CREATE FUNCTION function_name(parameters)
RETURNS datatype
LANGUAGE SQL
AS $$
    SQL Query
$$;

Example:

CREATE FUNCTION AddNumbers(a INT, b INT)
RETURNS INT
LANGUAGE SQL
AS $$
    SELECT a + b;
$$;

Execute

SELECT AddNumbers(10,20);

Output:

30

Why Use a Function?

• Perform calculations

• Reuse SQL code

• Reduce repeated queries

• Improve readability

• Return a value automatically

Advantage

• Reusable

• Easy to maintain

• Reduces coding

• Faster execution

• Returns a result