TEMPORARY FUNCTION IN SQL

Why use temporary functions?

You might ask “Do we really need functions in SQL?”,
absolutely yes. Temporary functions are important for several reasons:

• Organization -- As program grow in complexity, having all the queries in a big chunk increase difficult in understanding the program. Temporary function is almost like a mini-program that we can write separately from our main program, this allows us to reduce a complicated program into smaller, more manageable chunks, which reduces the overall complexity of our program.
• Reusability -- Once a temporary function is written, it can be called multiple times to the current session. This avoids duplicated queries (“Don’t Repeat Yourself”) and minimizes the probability of copy/paste errors.

Example of temporary function

Assume you are a data analyst of a wholesale company, having a database containing tables (Sales and Products). The “Sales” table contains information about sales transactions, including the product ID, quantity, and total price. The “Products” table holds product information, including the product ID, name, price, and discount. You want to know the price of products after discount.

CREATE OR REPLACE FUNCTION get_discounted_price(product_id INT)
RETURNS NUMERIC(10, 2)
AS
$$
DECLARE
  original_price NUMERIC(10, 2); 
  discount_rate NUMERIC(5, 2);
  discounted_price NUMERIC(10, 2);
BEGIN
  SELECT price, discount INTO original_price, discount_rate
  FROM Products
  WHERE product_id = product_id;

  discounted_price := original_price * (1 - discount_rate);

  RETURN discounted_price;
END;
$$
LANGUAGE plpgsql;

In the above code we create a temporary function named "get_discounted_price" that takes a product ID as a parameter and returns the price after applying the discount for that product.

We utilize our temporary function in our query as follows

SELECT product_id, product_name, get_discounted_price(product_id) AS discounted_price
FROM Products;

REMEMBER: Temporary functions are specific to the current session and will be automatically dropped when the session ends.