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();