public.inventory_in_stock
Documentation | Pagila |
Schema | public |
Name | inventory_in_stock |
Module | Functions |
The inventory_in_stock returns a boolean value indicating whether the inventory item specified is in stock.
Input/Output
Mode | Name | Data type | Description | |
---|---|---|---|---|
1 | OUT | Returns | boolean | Returns TRUE or FALSE to indicate whether the item specified is in stock. |
Returns TRUE or FALSE to indicate whether the item specified is in stock. | ||||
2 | IN | p_inventory_id | integer | The ID of the inventory item to be checked. |
The ID of the inventory item to be checked. |
Script
CREATE OR REPLACE FUNCTION public.inventory_in_stock(p_inventory_id integer) RETURNS boolean LANGUAGE plpgsql AS $function$ DECLARE v_rentals INTEGER; v_out INTEGER; BEGIN -- AN ITEM IS IN-STOCK IF THERE ARE EITHER NO ROWS IN THE rental TABLE -- FOR THE ITEM OR ALL ROWS HAVE return_date POPULATED SELECT count(*) INTO v_rentals FROM rental WHERE inventory_id = p_inventory_id; IF v_rentals = 0 THEN RETURN TRUE; END IF; SELECT COUNT(rental_id) INTO v_out FROM inventory LEFT JOIN rental USING(inventory_id) WHERE inventory.inventory_id = p_inventory_id AND rental.return_date IS NULL; IF v_out > 0 THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END $function$ |
Exported: 2018-10-12 17:07, Last imported: 2018-08-06 09:25