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.
The different types of user-defined functions are based on their return values.
The different return values can include:
Let’s have a look.
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;' LANGUAGE SQL; SELECT * FROM getStudent(1);
The output above would be empty, as no data is populated in the tables.
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; $$ LANGUAGE SQL; SELECT addDigits(1, 2) AS answer;
SELECT
statement, such as
SELECT * FROM getStudents(1);
HAVING
, WHERE
and UPDATE
clausesFree Resources