Contents
Solution: Server-Side Functions
Create an aggregate function to determine if
there are more male or female students in the workshop DB.
First execute in a unix shell: createlang plpgsql workshop
<![CDATA[
CREATE OR REPLACE FUNCTION count_f_m()
RETURNS varchar AS '
DECLARE table_row RECORD;
count_f INT;
count_m INT;
retstr varchar;
BEGIN
count_f := 0;
count_m := 0;
retstr := ''equal counts'';
FOR table_row IN SELECT gender FROM stud
LOOP
IF table_row.gender = ''f'' THEN count_f := count_f+1;
ELSIF table_row.gender = ''m'' THEN count_m := count_m+1;
END IF;
END LOOP;
IF count_f > count_m THEN retstr = ''female'';
ELSIF count_f < count_m THEN retstr = ''male'';
END IF;
RETURN retstr;
END;'
LANGUAGE 'plpgsql' ;
]]>
Within psql do:
\i filename.sql
SELECT count_f_m();