RETURNS

PostgreSQL

Example

CREATE OR REPLACE FUNCTION getFullName(firstName text, lastName text)
RETURNS text AS
$BODY$
BEGIN
   RETURN firstName || ' ' || lastName;
END;
$BODY$
LANGUAGE plpgsql;

SELECT getFullName('John', 'Doe');

Output

'John Doe'

Explanation

In the provided example, a function called getFullName is created, which concatenates two input texts firstName and lastName with a space in between. The RETURNS keyword indicates the data type of the output from this function, which in this case is ‘text’. The function is then invoked using a SELECT statement, returning ‘John Doe’.

SQL Server

Example

CREATE FUNCTION dbo.ProductPrice
  (@ProductID INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
  DECLARE @Price DECIMAL(10,2)

  SELECT @Price = Price FROM dbo.Products WHERE ProductID = @ProductID

  RETURN @Price
END;

Output

SELECT dbo.ProductPrice(5)

This will output the price of the product with ProductID 5, let’s say it’s 19.99.

19.99

Explanation

In the example above, a scalar function ProductPrice is created. This function takes one parameter @ProductID of type integer. The RETURNS keyword is used to specify the type of value the function will return, which is DECIMAL(10,2) in this case.

Inside the function, a variable @Price is declared to hold the price of the product and then the Price from the Products table is selected where ProductID equals the given @ProductID. This value is assigned to @Price. Finally, @Price is returned as the result of the function.

When we call the function with a specific ProductID (5 in this case), it outputs the price of that particular product.

Oracle

Example

CREATE OR REPLACE FUNCTION get_EmployeeName (p_id IN Employees.EmployeeID%TYPE)
RETURN Employees.FirstName%TYPE
IS
    v_name Employees.FirstName%TYPE;
BEGIN
    SELECT FirstName INTO v_name
    FROM Employees
    WHERE EmployeeID = p_id;

    RETURN v_name;
END;

Output

DECLARE
    v_name Employees.FirstName%TYPE;
BEGIN
    v_name := get_EmployeeName(5);
    DBMS_OUTPUT.PUT_LINE ('Employee Name: ' || v_name);
END;
```txt
Employee Name: David

Explanation

The above example illustrates the use of RETURNS in SQL. Here, a function get_EmployeeName is created that accepts an Employee ID as input (p_id) and returns the corresponding FirstName from the Employees table. In the output section, a DBMS_OUTPUT.PUT_LINE statement is used to display the FirstName of the employee with ID 5, which, in this example, is ‘David’.