7つのデータベース 7つの世界 2章 PostgreSQL 2日目 ref: http://qiita.com/items/8a00a1eb47921ffa49a5

add_event.sql

CREATE OR REPLACE FUNCTION add_event(title text, starts timestamp, ends timestamp, venue text, postal varchar(9), country char(2) )
RETURNS boolean AS $$

DECLARE
  did_insert boolean := false;
  found_cont integer;
  the_venue_id integer;
BEGIN
  SELECT venue_id INTO the_venue_id
  FROM venues v
  WHERE v.postal_code=postal AND v.country_code=country AND v.name ILike venue LIMIT 1;

  IF the_venue_id IS NULL THEN
    INSERT INTO venues (name, postal_code, country_code)
    VALUES (venue, postal, country)
    RETURNING venue_id INTO the_venue_id;

    did_insert := true;
  END IF;

  -- Note: not an "error", as in some programming languages
  RAISE NOTICE 'venue found %', the_venue_id;

  INSERT INTO events (title, starts, ends, venue_id)
  VALUES (title, starts, ends, the_venue_id);

  RETURN did_insert;
END;
$$ LANGUAGE plpgsql;

create_rule_insert.sql

CREATE RULE insert_holidays AS ON INSERT TO holidays DO INSTEAD
  INSERT INTO events(title, starts, colors)
  VALUES (NEW.name, NEW.date, NEW.colors);

create_rule_update.sql

CREATE RULE update_holidays AS ON UPDATE TO holidays DO INSTEAD
  UPDATE events
  SET title = NEW.name,
      starts = NEW.date,
      colors = NEW.colors
  WHERE title = OLD.name;

file0.sql

INSERT INTO venues(name, postal_code, country_code) 
  VALUES ('My Place', '97205', 'us');

file1.sql

INSERT INTO events (title, starts, ends, venue_id)
  VALUES ('Moby', '2012-02-06 21:00:00', '2012-02-06 23:00:00', (
   SELECT venue_id 
   FROM venues
   WHERE name = 'Crystal Ballroom'
  )
);

file10.sql

SELECT venue_id, count(*) 
FROM events
GROUP BY venue_id
ORDER BY venue_id;

file11.sql

SELECT title, venue_id, count(*) 
FROM events
GROUP BY venue_id;

file12.txt

ERROR:  column "events.title" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT title, venue_id, count(*) 

file13.sql

SELECT venue_id, title, count(*) OVER (PARTITION BY venue_id) FROM events;

file14.sql

BEGIN TRANSACTION;
  DELETE FROM events;
ROLLBACK;
SELECT * FROM events;

file16.sql

\i add_event.sql;
SELECT add_event('House Party', '2012-05-03 23:00', '2012-05-04 02:00', 'Run''s House', '97205', 'us');

file17.txt

NOTICE:  venue found 5
 add_event 
-----------
 t
(1 row)

file18.sql

CREATE TABLE logs(
  event_id integer,
  old_title varchar(255),
  old_starts timestamp,
  old_ends timestamp,
  logged_at timestamp DEFAULT current_timestamp
);

file2.sql

INSERT INTO events (title, starts, ends, venue_id)
  VALUES ('Wedding', '2012-02-26 21:00:00', '2012-02-26 23:00:00', 2), ('Dinner with Mam', '2012-02-26 18:00:00', '2012-02-26 20:30:00', 3), ('Valentine''s Day', '2012-02-14 00:00:00', '2012-02-14 23:59:00',NULL);

file20.sql

CREATE TRIGGER log_events 
  AFTER UPDATE ON events
  FOR EACH ROW EXECUTE PROCEDURE log_event();

file21.sql

UPDATE events 
SET ends='2012-05-04 01:00:00'
WHERE title='House Party';

file22.txt

NOTICE:  Someone just changed event #8
UPDATE 1

file23.sql

SELECT event_id, old_title, old_starts, old_ends, logged_at FROM logs;

file24.txt

 event_id |  old_title  |     old_starts      |      old_ends       |         logged_at          
----------+-------------+---------------------+---------------------+----------------------------
        8 | House Party | 2012-05-03 23:00:00 | 2012-05-04 02:00:00 | 2013-03-25 13:56:57.067047
(1 row)

file26.sql

SELECT name, to_char(date, 'Month DD, YYY') As date 
FROM holidays 
WHERE date <= '2012-04-01';

file27.txt

      name       |       date        
-----------------+-------------------
 April Fools Day | April     01, 012
 Valentine's Day | February  14, 012
(2 rows)

file28.sql

ALTER TABLE events 
ADD colors text ARRAY;

CREATE OR REPLACE VIEW holidays AS
  SELECT event_id AS holiday_id, title AS name, starts AS date, colors 
  FROM events
  WHERE title Like '%Day%' AND venue_id IS NULL;

file29.sql

UPDATE holidays SET colors = '{"red", "green"}' where name = 'Christmas Day';

file3.sql

SELECT count(title) 
FROM events 
WHERE title LIKE '%Day%';

file30.txt

ERROR:  cannot update view "holidays"
HINT:  You need an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger.

file31.sql

EXPLAIN VERBOSE
  SELECT * 
  FROM holidays;
EXPLAIN VERBOSE
  SELECT event_id AS holiday_id, title AS name, starts AS date, colors 
  FROM events
  WHERE title Like '%Day%' AND venue_id IS NULL;

file34.sql

INSERT INTO holidays (name, date, colors) 
VALUES ('New Year Day', '2013-01-01 00:00:00', '{"white","orange"}');

file35.sql

SELECT extract(year from starts) as year,
  extract(month from starts) as month, count(*) 
FROM events
GROUP BY year, month;

file36.sql

CREATE TEMPORARY TABLE month_count(month INT);
INSERT INTO month_count VALUES (1), (2), (3), (4), (5), (6), (7), (8),(9), (10), (11), (12);

file37.sql

SELECT * FROM crosstab(
  'SELECT extract(year from starts) as year,
  extract(month from starts) as month, count(*) 
   FROM events
   GROUP BY year, month',
  'SELECT * FROM month_count'
) AS (
  year int,
  jan int, feb int, mar int, apr int, may int, jun int, 
  jul int, aug int, sep int, oct int, nov int, dec int
) ORDER BY YEAR;

file38.sql

DROP TRIGGER logical_delete ON venues;
CREATE TRIGGER logical_delete
  BEFORE DELETE ON venues
  FOR EACH ROW EXECUTE PROCEDURE logical_delete();

file39.sql

INSERT INTO venues (name, postal_code, country_code) VALUES('My Home', '97205', 'us');
DELETE FROM venues WHERE name = 'My Home';

file4.txt

 count 
-------
     3
(1 row)

file40.sql

SELECT * FROM crosstab(
  'SELECT extract(year from starts) as year,
          extract(month from starts) as month, count(*) 
   FROM events
   GROUP BY year, month',
  'SELECT generate_series(1, 12)'
) AS (
  year int, 
  jan int, feb int, mar int, apr int, may int, jun int, 
  jul int, aug int, sep int, oct int, nov int, dec int
) ORDER BY YEAR;

file41.sql

SELECT * FROM crosstab(
  'SELECT extract(week from starts) as week,
          extract(dow from starts) as dow,
          count(*) 
   FROM events
   WHERE starts >= ''2012-02-01'' AND ''2012-03-01'' > starts
   GROUP BY week, dow
   ORDER BY week, dow',
  'SELECT generate_series(0,6)'
) AS (
  week int,
  sun int, mon int, tue int, wed int, thu int, fri int, sat int
) ORDER BY WEEK;

file5.sql

SELECT min(starts), max(ends) 
FROM events INNER JOIN venues
  ON events.venue_id = venues.venue_id 
WHERE venues.name = 'Crystal Ballroom';

file6.sql

SELECT venue_id, count(*) 
FROM events 
GROUP BY venue_id 
ORDER BY venue_id;

file7.sql

SELECT DISTINCT venue_id FROM events GROUP BY venue_id;

file8.sql

SELECT venue_id, count(*) 
FROM events
GROUP BY venue_id 
HAVING count(*) >= 2 AND venue_id IS NOT NULL;

file9.sql

SELECT venue_id, count(*) 
 OVER (PARTITION BY venue_id) 
FROM events
ORDER BY venue_id;

holiday_view_1.sql

CREATE VIEW holidays AS
  SELECT event_id AS holiday_id, title AS name, starts AS date 
  FROM events
  WHERE title Like '%Day%' AND venue_id IS NULL;

log_event.sql

CREATE OR REPLACE FUNCTION log_event() RETURNS trigger AS $$
DECLARE
BEGIN
  INSERT INTO logs (event_id, old_title, old_starts, old_ends)
  VALUES (OLD.event_id, OLD.title, OLD.starts, OLD.ends);
  RAISE NOTICE 'Someone just changed event #%', OLD.event_id;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;