It's Autumn! Prices fall like leaves! - hours only!Up to 80% off on all courses and bundles.-Close
Introduction
Side-effects in functions
Invoking functions

Instruction

Good job! Functions are powerful because they allow you to reuse existing code when writing new functions.

In this section, we'll look at various ways you can use and reuse query results in functions and how you can invoke functions within each other in PL/pgSQL.

CREATE FUNCTION create_shipment(customer_id integer, prod_id integer, quantity integer)
RETURNS void AS $$
DECLARE
  stock_quantity integer;
  shipment_address varchar(2000);
  contact_number varchar(20);
BEGIN
  SELECT
    address,
    phone
  INTO
   shipment_address,
   contact_number
  FROM customer
  WHERE customer_id = cust_id;

  stock_quantity := get_stock_quantity(prod_id);
  IF (stock_quantity < quantity) 
  THEN
     RAISE EXCEPTION 'Not enough product in stock %', prod_id;
  END IF;

  PERFORM add_shipment(prod_id, quantity, customer_id, shipment_address, contact_number);
END;
$$ LANGUAGE plpgsql;

This example shows various ways of calling complex statements in a function. In this section, we'll look in detail at the syntax of complex statements in PostgreSQL and PL/pgSQL.

Exercise

Click Next exercise to continue.