Working with Functions

CREATE OR REPLACE FUNCTION add(a int, b int) RETURNS int
LANGUAGE sql IMMUTABLE AS $
   SELECT a + b;
$;

CREATE FUNCTION square(n int) RETURNS int LANGUAGE sql AS $ SELECT n*n $;

CREATE FUNCTION top_customers(lim int)
RETURNS TABLE(id bigint, name text, total numeric)
LANGUAGE sql AS $
   SELECT c.id, c.full_name, sum(o.total_cents)
     FROM customers c JOIN orders o ON o.customer_id = c.id
   GROUP BY c.id, c.full_name
   ORDER BY 3 DESC LIMIT lim;
$;
SELECT * FROM top_customers(10);

CREATE FUNCTION primes_up_to(n int) RETURNS SETOF int
LANGUAGE sql AS $
   SELECT g FROM generate_series(2,n) g
    WHERE NOT EXISTS (
       SELECT 1 FROM generate_series(2, floor(sqrt(g))::int) d
        WHERE g % d = 0);
$;

CREATE OR REPLACE FUNCTION transfer(from_id int, to_id int, amt numeric)
RETURNS void LANGUAGE plpgsql AS $
BEGIN
   UPDATE accounts SET bal = bal - amt WHERE id = from_id;
   UPDATE accounts SET bal = bal + amt WHERE id = to_id;
   IF NOT FOUND THEN RAISE EXCEPTION 'Account % not found', to_id; END IF;
END $;

DECLARE
   count_var  int := 0;
   row_var    customers%ROWTYPE;
   email_var  customers.email%TYPE;

x := 5;
SELECT count(*) INTO count_var FROM orders;
GET DIAGNOSTICS row_count = ROW_COUNT;

IF x > 0 THEN
   RAISE NOTICE 'positive';
ELSIF x = 0 THEN
   RAISE NOTICE 'zero';
ELSE
   RAISE NOTICE 'negative';
END IF;

CASE x
   WHEN 1 THEN RAISE NOTICE 'one';
   WHEN 2,3 THEN RAISE NOTICE 'two or three';
   ELSE RAISE NOTICE 'other';
END CASE;

LOOP
   i := i + 1;
   EXIT WHEN i > 10;
END LOOP;

FOR i IN 1..10 LOOP RAISE NOTICE '%', i; END LOOP;
FOR r IN SELECT * FROM customers LOOP
   PERFORM process(r);
END LOOP;

WHILE balance > 0 LOOP
   balance := balance - 10;
END LOOP;

FOREACH tag IN ARRAY ARRAY['a','b','c'] LOOP
   INSERT INTO tags(name) VALUES (tag);
END LOOP;