Tags:conceptdatabasesqlfunctionssqlfunctions Status:🟩

---

SQL Functions

Summary

SQL Functions allow for taking inputs and returning a value or performing an action. They are particularly useful for executing the same SQL query multiple times with different values, which can be a time-saving process when dealing with large datasets.

Details

• Functions can be created to perform repetitive actions or computations within a database.
• Functions are faster than executing SQL queries from external clients since the data doesn’t need to move out of the database.
• They may be pre-compiled and optimized for performance, reducing the need for repeated optimization.
• They can offer performance improvements and code reusability but may be system-specific and require careful maintenance.

Identity Columns

When dealing with identity columns, it’s best to use GENERATED ALWAYS AS IDENTITY to avoid issues when inserting rows into the table.

Function Speed

• Avoids data transfer between client and database.
• Functions may be pre-compiled for faster execution, especially with proper query optimization.

Pros and Cons

Pros

• Shared code across all applications.
• Can be used for access control.
• May provide performance benefits when optimized.

Cons

• System-specific implementations can be limiting.
• Requires careful code maintenance.
• Versioning can be difficult.

Examples

Table Structure

RecordLog (
  peopleID INT,
  competitionID INT,
  sportID INT,
  oldrecord FLOAT,
  newrecord FLOAT,
  seton DATE
);

Example Function: Find Biggest Record Jump

The function calculates the largest record improvement (new record minus old record) for a given sport based on sportID.

DROP FUNCTION IF EXISTS BiggestRecordJump();
CREATE FUNCTION BiggestRecordJump(
  IN sid INT
)
RETURNS FLOAT
AS $$
DECLARE r FLOAT;
BEGIN
  SELECT MAX(newrecord - oldrecord) INTO r
  FROM RecordLog
  WHERE sportID = sid;
  RETURN r;
END;
$$ LANGUAGE plpgsql;
 
SELECT BiggestRecordJump(1);

Using a Function

Here’s an example of how to call a function using SQL:

SELECT NewPerson('Terry', 'M', 1.77);
SELECT * FROM NewPerson('Terry', 'M', 1.77);
 
DO $$
BEGIN
  PERFORM NewPerson('Terry', 'M', 1.77);
END
$$;