반응형
/****************************************************************************************************************
-- 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;



반응형

+ Recent posts