#   
## 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.



## 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;' 
LANGUAGE SQL;

SELECT * FROM getStudent(1);

import React from 'react';
import { shallow, configure } from 'enzyme';
import Adapter from 'enzyme-adapter-react-16';

configure({ adapter: new Adapter() });

import HelloWorld from './app';

var TestResult = function() {
    this.succeeded = false;
    this.reason = "";
    this.input = "";
    this.expected_output = "";
    this.actual_output = "";
}

export const executeTests = function() {

  var results = [];

  result = new TestResult();
  result.input = 'HelloWorld Component';
  result.expected_output = "span containing text 'Hello World'"

  let wrapper = shallow(<HelloWorld />);

  // Call your Challenge function here.

  let type = wrapper.type();
  let testSuccessful = true;
  let failureReason;

  if (type !== 'span') {
    testSuccessful = false;
    failureReason = "You need to render exactly one span HTML element";
  } else if (wrapper.props().children != "Hello World") {
     testSuccessful = false;
     failureReason = "You have rendered wrong message in your span element";
  }

  result.actual_output = wrapper.html();

  if (testSuccessful) {
    result.succeeded = true;
    result.reason = "Succeeded"
  } else {
    result.succeeded = false;
    result.reason = failureReason;
  }

  results.push(result);

  return results;
}


postgres

React

Dart

GoJS React

Typescript React

Vue.js

> 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;
$$ LANGUAGE SQL;

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

sql_img.tar.gz

What are user-defined functions in SQL?

User-defined SQL functions take input parameters to perform calculations and return tables, result-sets, or scalar values, with key differences from stored procedures.