a shot of dev knowledge


What are user-defined functions in SQL?

Defining user-defined functions

User-defined functions in SQL are similar to functions and methods in any other language. They take input parameters from the user to perform complex calculations and return the required value.

There are different types of return values that we will look at later in this lesson.

Properties of user-defined functions

Types of user-defined functions

The different types of user-defined functions are based on their return values.

The different return values can include:

  • A table
  • A result-set
  • A scalar value

Let’s have a look.

Table-valued functions

Table-valued functions take zero or more inputs from the user to return a result-set of data.

This result can be considered a table and be used for joins.

CREATE TABLE Student (rollNo int, courseId int, sName text);

CREATE FUNCTION getStudent(int) RETURNS setof Student AS 
  'SELECT * FROM Student WHERE rollNo = $1;' 

SELECT * FROM getStudent(1);

The output above would be empty, as no data is populated in the tables.

Scalar functions

Scalar functions take zero or more inputs from the user to return an output.

This output would be any value that corresponds to scalar data types.

CREATE FUNCTION addDigits(int, int) RETURNS int AS $$
    SELECT $1 + $2;

SELECT addDigits(1, 2) AS answer;

Difference between stored procedures and user-defined functions

  • Stored procedures are compiled once, while user-defined functions are compiled and executed on runtime
  • User-defined functions would return a value, while stored procedures may not return any output values
  • Temporary variables cannot be a part of user-defined function, while stored procedures can make use of them
  • In contrast with stored procedures, exception handling is not catered within a user-defined block

Advantages of user-defined functions

  • User-defined functions can be executed directly as a part of a SELECT statement, such as SELECT * FROM getStudents(1);
  • User-defined functions can be executed from HAVING, WHERE and UPDATE clauses



View all Courses

Keep Exploring