[PGS9.2] FUNCTION Samples

/****************************************************************************************************************
-- Title : [PGS9.2] FUNCTION Samples

-- Reference : 웹검색
-- Key word : create function 함수
****************************************************************************************************************/

CREATE OR REPLACE FUNCTION fn_group_concat(text, text) 

RETURNS text 

IMMUTABLE

AS 

$$

    SELECT CASE WHEN $2 IS NULL THEN $1

                WHEN $1 IS NULL THEN $2

                ELSE $1 || ', ' || $2

           END

$$ LANGUAGE sql;

ALTER FUNCTION public.fn_group_concat(text, text) OWNER TO pgsys;

CREATE OR REPLACE FUNCTION fn_inventory_in_stock

    (p_inventory_id int) 

RETURNS boolean

AS 

$$

DECLARE

    v_rentals int;

    v_out     int;

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 sch_film.rental

    WHERE inventory_id = p_inventory_id;

    IF v_rentals = 0 THEN

      RETURN TRUE;

    END IF;

    SELECT COUNT(rental_id) INTO v_out

    FROM sch_film.inventory i

    LEFT JOIN sch_shop.rental r 

    ON i.inventory_id = r.inventory_id

    WHERE i.inventory_id = p_inventory_id

    AND r.return_date IS NULL;

    IF v_out > 0 THEN

      RETURN FALSE;

    ELSE

      RETURN TRUE;

    END IF;

END 

$$ LANGUAGE plpgsql;

ALTER FUNCTION public.fn_inventory_in_stock(p_inventory_id int) OWNER TO pgsys;

CREATE OR REPLACE FUNCTION fn_film_in_stock

    (p_film_id int, p_store_id int, OUT p_film_count int) 

RETURNS SETOF int

AS 

$$

     SELECT inventory_id

     FROM sch_film.inventory

     WHERE film_id = $1

     AND store_id = $2

     AND fn_inventory_in_stock(inventory_id);

$$ LANGUAGE sql;

ALTER FUNCTION public.fn_film_in_stock(p_film_id int, p_store_id int, OUT p_film_count int) OWNER TO pgsys;

CREATE OR REPLACE FUNCTION fn_film_not_in_stock

    (p_film_id int, p_store_id int, OUT p_film_count int) 

RETURNS SETOF int

AS 

$$

    SELECT inventory_id

    FROM sch_film.inventory

    WHERE film_id = $1

    AND store_id = $2

    AND NOT fn_inventory_in_stock(inventory_id);

$$ LANGUAGE sql;

ALTER FUNCTION public.fn_film_not_in_stock(p_film_id int, p_store_id int, OUT p_film_count int) OWNER TO pgsys;

CREATE OR REPLACE FUNCTION fn_get_customer_balance

    (p_customer_id int, p_effective_date timestamp without time zone) 

RETURNS numeric

AS 

$$

    --# OK, WE NEED TO CALCULATE THE CURRENT BALANCE GIVEN A CUSTOMER_ID AND A DATE

    --# THAT WE WANT THE BALANCE TO BE EFFECTIVE FOR. THE BALANCE IS:

    --# 1) RENTAL FEES FOR ALL PREVIOUS RENTALS

    --# 2) ONE DOLLAR FOR EVERY DAY THE PREVIOUS RENTALS ARE OVERDUE

    --# 3) IF A FILM IS MORE THAN RENTAL_DURATION * 2 OVERDUE, CHARGE THE REPLACEMENT_COST

    --# 4) SUBTRACT ALL PAYMENTS MADE BEFORE THE DATE SPECIFIED

DECLARE

    v_rentfees DECIMAL(5,2); --#FEES PAID TO RENT THE VIDEOS INITIALLY

    v_overfees int;          --#LATE FEES FOR PRIOR RENTALS

    v_payments DECIMAL(5,2); --#SUM OF PAYMENTS MADE PREVIOUSLY

BEGIN

    SELECT COALESCE(SUM(film.rental_rate),0) INTO v_rentfees

    FROM sch_film.film, sch_film.inventory, sch_shop.rental

    WHERE film.film_id = inventory.film_id

      AND inventory.inventory_id = rental.inventory_id

      AND rental.rental_date <= p_effective_date

      AND rental.customer_id = p_customer_id;

    SELECT COALESCE(SUM(IF((rental.return_date - rental.rental_date) > (film.rental_duration * '1 day'::interval)

                          ,((rental.return_date - rental.rental_date) - (film.rental_duration * '1 day'::interval)),0

                          )

                       ),0

                   ) INTO v_overfees

    FROM rental, inventory, film

    WHERE film.film_id = inventory.film_id

      AND inventory.inventory_id = rental.inventory_id

      AND rental.rental_date <= p_effective_date

      AND rental.customer_id = p_customer_id;

    SELECT COALESCE(SUM(payment.amount),0) INTO v_payments

    FROM payment

    WHERE payment.payment_date <= p_effective_date

    AND payment.customer_id = p_customer_id;

    RETURN v_rentfees + v_overfees - v_payments;

END

$$ LANGUAGE plpgsql;

ALTER FUNCTION public.fn_get_customer_balance(p_customer_id int, p_effective_date timestamp without time zone) OWNER TO pgsys;

CREATE OR REPLACE FUNCTION fn_inventory_held_by_customer

    (p_inventory_id int) 

RETURNS int

AS 

$$

DECLARE

    v_customer_id int;

BEGIN

  SELECT customer_id INTO v_customer_id

  FROM sch_shop.rental

  WHERE return_date IS NULL

  AND inventory_id = p_inventory_id;

  RETURN v_customer_id;

END 

$$ LANGUAGE plpgsql;

ALTER FUNCTION public.fn_inventory_held_by_customer(p_inventory_id int) OWNER TO pgsys;

CREATE OR REPLACE FUNCTION fn_last_day

    (timestamp without time zone) 

RETURNS date

IMMUTABLE 

STRICT

AS 

$$

  SELECT CASE WHEN EXTRACT(MONTH FROM $1) = 12 

              THEN (((EXTRACT(YEAR FROM $1) + 1) operator(pg_catalog.||) '-01-01')::date - INTERVAL '1 day')::date

              ELSE ((EXTRACT(YEAR FROM $1) operator(pg_catalog.||) '-' operator(pg_catalog.||) (EXTRACT(MONTH FROM $1) + 1) operator(pg_catalog.||) '-01')::date - INTERVAL '1 day')::date

         END

$$ LANGUAGE sql ;

ALTER FUNCTION public.fn_last_day(timestamp without time zone) OWNER TO pgsys;

CREATE OR REPLACE FUNCTION fn_last_updated() 

RETURNS trigger

AS 

$$

BEGIN

    NEW.last_update = CURRENT_TIMESTAMP;

    RETURN NEW;

END 

$$ LANGUAGE plpgsql;

ALTER FUNCTION public.fn_last_updated() OWNER TO pgsys;