postgres tutorial 11 functions

CREATE FUNCTION function_name(p1 type, p2 type)
 RETURNS type AS
BEGIN
 -- logic
END;
LANGUAGE language_name;

CREATE OR REPLACE FUNCTION get_sum(
 a NUMERIC,
 b NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
 RETURN a + b;
END; $$

LANGUAGE plpgsql;

select get_sum(2,3);
-----------------------------------

CREATE OR REPLACE FUNCTION hi_lo(
 a NUMERIC,
 b NUMERIC,
 c NUMERIC,
        OUT hi NUMERIC,
 OUT lo NUMERIC)
AS $$
BEGIN
 hi := GREATEST(a,b,c);
 lo := LEAST(a,b,c);
END; $$

LANGUAGE plpgsql;

SELECT hi_lo(10,20,30);

(30,10)

---------------------------------------
CREATE OR REPLACE FUNCTION square(
 INOUT a NUMERIC)
AS $$
BEGIN
 a := a * a;
END; $$
LANGUAGE plpgsql;

SELECT square(4);

--------------------
accept a variable number of arguments with one condition that all arguments have the same data type
The sum_avg() function accepts a list of numbers, calculates the total and average, and returns both values.

CREATE OR REPLACE FUNCTION sum_avg(
 VARIADIC list NUMERIC[],
 OUT total NUMERIC,
        OUT average NUMERIC)
AS $$
BEGIN
   SELECT INTO total SUM(list[i])
   FROM generate_subscripts(list, 1) g(i);

   SELECT INTO average AVG(list[i])
   FROM generate_subscripts(list, 1) g(i);

END; $$
LANGUAGE plpgsql;

SELECT * FROM sum_avg(10,20,30);
total | average
60 20

--------------------------------
CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR)
 RETURNS TABLE (
 film_title VARCHAR,
 film_release_year INT
)
AS $$
BEGIN
 RETURN QUERY SELECT
 title,
 cast( release_year as integer)
 FROM
 film
 WHERE
 title ILIKE p_pattern ;
END; $$

LANGUAGE 'plpgsql';

-------------------------
SELECT
 *
FROM
 get_film ('Al%');

---------------------
CREATE OR REPLACE FUNCTION get_film (p_pattern VARCHAR,p_year INT)
 RETURNS TABLE (
 film_title VARCHAR,
 film_release_year INT
) AS $$
DECLARE
    var_r record;
BEGIN
 FOR var_r IN(SELECT
 title,
 release_year
                FROM film
 WHERE title ILIKE p_pattern AND
 release_year = p_year)
 LOOP
        film_title := upper(var_r.title) ;
 film_release_year := var_r.release_year;
        RETURN NEXT;
 END LOOP;
END; $$
LANGUAGE 'plpgsql';

--------------------------------
SELECT
 *
FROM
 get_film ('%er', 2006);

--------------------------------------------
CREATE OR REPLACE FUNCTION get_customer_service (p_customer_id INTEGER)
 RETURNS VARCHAR (25) AS $$
DECLARE
    total_payment NUMERIC ;
    service_level VARCHAR (25) ;
BEGIN
 -- get the rate based on film_id
     SELECT
 INTO total_payment SUM (amount)
     FROM
 payment
     WHERE
 customer_id = p_customer_id ;

   CASE
      WHEN total_payment > 200 THEN
         service_level = 'Platinum' ;
      WHEN total_payment > 100 THEN
 service_level = 'Gold' ;
      ELSE
         service_level = 'Silver' ;
   END CASE ;

   RETURN service_level ;
END ; $$
LANGUAGE plpgsql;

---------------------------------------------
CREATE OR REPLACE FUNCTION fibonacci (n INTEGER)
 RETURNS INTEGER AS $$
DECLARE
   counter INTEGER := 0 ;
   i INTEGER := 0 ;
   j INTEGER := 1 ;
BEGIN

 IF (n < 1) THEN
 RETURN 0 ;
 END IF;

 LOOP 
 EXIT WHEN counter = n ;
 counter := counter + 1 ;

 SELECT j, i + j INTO i, j ;
 END LOOP ;

 RETURN i ;
END ;
$$ LANGUAGE plpgsql;

select fibonacci(6);
8

---------------------------------------
DO $$
BEGIN
  FOR counter IN 1..6 BY 2 LOOP
    RAISE NOTICE 'Counter: %', counter;
  END LOOP;
END; $$

NOTICE:  Counter: 1
NOTICE:  Counter: 3
NOTICE:  Counter: 5

DO $$
BEGIN
   FOR counter IN REVERSE 5..1 LOOP
      RAISE NOTICE 'Counter: %', counter;
   END LOOP;
END; $$

NOTICE:  Counter: 5
NOTICE:  Counter: 4
NOTICE:  Counter: 3
NOTICE:  Counter: 2
NOTICE:  Counter: 1

----------------------------------------
CREATE OR REPLACE FUNCTION for_loop_through_query(
   n INTEGER DEFAULT 10
)
RETURNS VOID AS $$
DECLARE
    rec RECORD;
BEGIN
    FOR rec IN SELECT title
        FROM film
        ORDER BY title
        LIMIT n
    LOOP
 RAISE NOTICE '%', rec.title;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

SELECT for_loop_through_query(5);

NOTICE:  Academy Dinosaur
NOTICE:  Ace Goldfinger
NOTICE:  Adaptation Holes
NOTICE:  Affair Prejudice
NOTICE:  African Egg