Code for snacks.txt

/* filename: snacks.sql */

/*
 * A simple exploratory database
 */

/* 
 * The system that we will be using plpgsql scripts
 */
create or replace language plpgsql;
 
/*
 * Create blank schema for holding this simple example
 */ 
DROP SCHEMA IF EXISTS play CASCADE;
CREATE SCHEMA play; 

/* 
 * Create a simple table for holding information about snacks 
 */
CREATE TABLE play.snack (
id		 	SERIAL PRIMARY KEY, 
name 		varchar(64) UNIQUE NOT NULL,
calories 	integer CHECK (calories > 0),
price 		numeric(8,2)
);

/* 
 * Returns the number of snacks in the database 
 */
CREATE OR REPLACE FUNCTION play.howManySnacks ()
RETURNS INTEGER AS '
DECLARE
        num     integer;
BEGIN
        select into num count(*) from play.snack;
        return num;
END;
' LANGUAGE plpgsql;


/*
 * Load some data into the table 
 */
INSERT INTO play.snack (name, calories, price) VALUES ('Chips', 200, 3.50);
INSERT INTO play.snack (name, calories, price) VALUES ('Cheezees', 200, 3.50);
INSERT INTO play.snack (name, calories, price) VALUES ('Bar', 300, 2.00); 
INSERT INTO play.snack (name, calories, price) VALUES ('Fries', 500, 4.25); 

/*
 * Query the database 
 */
select * from play.snack;
select * from play.howManySnacks();