-- Examples of functions and triggers in PL/pgSQL.
--
-- Some of the functions require the University sample database,
-- so run university-create.sql first before running this script.
--
--      csun@calstatela.edu, 10/20/2010

create or replace function hello() returns void as $$
begin
    raise notice 'Hello world in PL/pgSQL';
end;
$$ language plpgsql;

create or replace function add10( a integer ) returns integer as $$
declare
    b   integer;
begin
    b := 10;
    return a + b;
end;
$$ language plpgsql;

create or replace function add( p_a integer, p_b integer ) returns void as $$
declare
    l_sum integer;
begin
    l_sum := p_a + p_b;
    raise notice 'The sum of % and % is %.', p_a, p_b, l_sum;
end;
$$ language plpgsql;

create or replace function student_name( p_id integer ) returns varchar as $$
declare
    l_name  varchar;
begin
    select name into l_name from students where id = p_id;
    if l_name is null then
        raise warning 'no student found with id %', p_id;
    end if;
    return l_name;
end;
$$ language plpgsql;

create or replace function fact1( p_n integer ) returns integer as $$
declare
    l_fact    integer default 1;
    l_n       integer;
begin
    if p_n < 1 then
        raise warning 'n must be at least 1';
        return null;
    end if;

    for l_n in 1..p_n loop
        l_fact := l_fact * l_n;
    end loop;

    return l_fact;
end;
$$ language plpgsql;

create or replace function fact2( p_n integer ) returns integer as $$
begin
    if p_n < 1 then
        raise warning 'n must be at least 1';
        return null;
    elsif p_n = 1 then
        return 1;
    else
        return p_n * fact2(p_n-1);
    end if;
end;
$$ language plpgsql;

create or replace function two_students() returns setof students as $$
declare
    l_student   students%rowtype;
    l_id        students.id%type;
    l_max_id    students.id%type;
begin
    select max(id) into l_max_id from students;
    l_id = 1+ trunc( random() * l_max_id );
    select * into l_student from students where id = l_id;
    return next l_student;
    l_id = 1+ trunc( random() * l_max_id );
    select * into l_student from students where id = l_id;
    return next l_student;
    return;
end;
$$ language plpgsql;

create or replace function student_samples() returns setof students as $$
declare
    l_student   students%rowtype;
    l_cursor    refcursor;
begin
    open l_cursor for select * from students;
    loop
        fetch l_cursor into l_student;
        exit when not found;
        if random() <= 0.2 then
            return next l_student;
        end if;
    end loop;
    close l_cursor;
    return;
end;
$$ language plpgsql;

create or replace function student_samples2() returns setof students as $$
declare
    l_student   students%rowtype;
begin
    for l_student in select * from students loop
        if random() <= 0.2 then
            return next l_student;
        end if;
    end loop;
end;
$$ language plpgsql;

create table grade_changes (
    enrollment_id       integer,
    old_grade_id        integer,
    new_grade_id        integer,
    timestamp           timestamp
);

create or replace function grade_audit() returns trigger as $$
begin
    if new.id = old.id and new.grade_id <> old.grade_id then
        insert into grade_changes values (
            new.id, old.grade_id, new.grade_id, current_timestamp );
    end if;
    return null;
end;
$$ language plpgsql;

create trigger grade_audit
    after update
    on enrollment
    for each row
    execute procedure grade_audit();

create or replace function db_class_size() returns trigger as $$
declare
    l_class_title   courses.title%type;
    l_class_size    integer;
begin
    select title into l_class_title from courses c, sections s
        where s.id = new.section_id and s.course_id = c.id;
    if l_class_title = 'Databases' then
        select count(student_id) into l_class_size from enrollment
            where section_id = new.section_id;
        if l_class_size >= 30 then
            raise exception 'Database class cannot take more than 30 students.';
        end if;
    end if;
    return new;
end;
$$ language plpgsql;

create trigger db_class_size
    before insert or update
    on enrollment
    for each row
    execute procedure db_class_size();