public.inventory_held_by_customer

Documentation	Pagila
Schema	public
Name	inventory_held_by_customer
Module	Functions

The inventory_held_by_customer returns the customer_id of the customer who has rented out the specified inventory item.

Input/Output

	Mode	Name	Data type	Description
1	OUT	Returns	integer	The ID of the inventory item to be checked.
The ID of the inventory item to be checked.
2	IN	p_inventory_id	integer	Returns the customer_id of the customer who is currently renting the item, or NULL if the item is in stock.
Returns the customer_id of the customer who is currently renting the item, or NULL if the item is in stock.

Script

CREATE OR REPLACE FUNCTION public.inventory_held_by_customer(p_inventory_id integer) RETURNS integer LANGUAGE plpgsql AS $function$ DECLARE v_customer_id INTEGER; BEGIN SELECT customer_id INTO v_customer_id FROM rental WHERE return_date IS NULL AND inventory_id = p_inventory_id; RETURN v_customer_id; END $function$

CREATE OR REPLACE FUNCTION public.inventory_held_by_customer(p_inventory_id integer)
 RETURNS integer
 LANGUAGE plpgsql
AS $function$
DECLARE
    v_customer_id INTEGER;
BEGIN

  SELECT customer_id INTO v_customer_id
  FROM rental
  WHERE return_date IS NULL
  AND inventory_id = p_inventory_id;

  RETURN v_customer_id;
END $function$

Exported: 2018-10-12 17:07, Last imported: 2018-08-06 09:25