Deals Of The Week - hours only!Up to 80% off on all courses and bundles.-Close

Wrap course as a gift

Prepare your gift and double-check it looks just right.

Form validation errors

Recipient's Name:
Recipient's Email:
Your Message (Optional):
Proceed to Checkout

Writing User-Defined Functions in PostgreSQL

Introduction
Syntax of PL/pgSQL
Function parameters
15. Input parameters
Handling exceptions
Summary

Instruction

Good job! In this section, we'll discuss various ways that you can pass arguments into a PL/pgSQL function.

As we already know, we can specify some arguments after the function's name. We can also refer to the arguments in the function body.

The circle_area() function accepts one input parameter named radius of type decimal.

CREATE FUNCTION circle_area(radius IN decimal)
RETURNS decimal AS $$
BEGIN
  RETURN pi() * pow(radius, 2);
END;
$$ LANGUAGE plpgsql;

Note that we specified the parameter as radius IN decimal. The IN keyword means that radius is an input parameter; it can be passed to a function, but it will not be returned back. By default, parameters in PostgreSQL are IN parameters.

Exercise

Create a function named get_count_of_earners(salary_val IN decimal) that takes one IN parameter named salary_val of type decimal and returns the number of records from the salary table for which value is greater than or equal to the salary_val parameter.