Created
March 26, 2013 15:17
-
-
Save hrstt/5246175 to your computer and use it in GitHub Desktop.
7つのデータベース 7つの世界 2章 PostgreSQL 2日目 ref: http://qiita.com/items/8a00a1eb47921ffa49a5
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE RULE insert_holidays AS ON INSERT TO holidays DO INSTEAD | |
INSERT INTO events(title, starts, colors) | |
VALUES (NEW.name, NEW.date, NEW.colors); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
INSERT INTO venues(name, postal_code, country_code) | |
VALUES ('My Place', '97205', 'us'); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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' | |
) | |
); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT venue_id, count(*) | |
FROM events | |
GROUP BY venue_id | |
ORDER BY venue_id; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT title, venue_id, count(*) | |
FROM events | |
GROUP BY venue_id; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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(*) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT venue_id, title, count(*) OVER (PARTITION BY venue_id) FROM events; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
BEGIN TRANSACTION; | |
DELETE FROM events; | |
ROLLBACK; | |
SELECT * FROM events; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
\i add_event.sql; | |
SELECT add_event('House Party', '2012-05-03 23:00', '2012-05-04 02:00', 'Run''s House', '97205', 'us'); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
NOTICE: venue found 5 | |
add_event | |
----------- | |
t | |
(1 row) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TABLE logs( | |
event_id integer, | |
old_title varchar(255), | |
old_starts timestamp, | |
old_ends timestamp, | |
logged_at timestamp DEFAULT current_timestamp | |
); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TRIGGER log_events | |
AFTER UPDATE ON events | |
FOR EACH ROW EXECUTE PROCEDURE log_event(); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
UPDATE events | |
SET ends='2012-05-04 01:00:00' | |
WHERE title='House Party'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
NOTICE: Someone just changed event #8 | |
UPDATE 1 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT event_id, old_title, old_starts, old_ends, logged_at FROM logs; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT name, to_char(date, 'Month DD, YYY') As date | |
FROM holidays | |
WHERE date <= '2012-04-01'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
name | date | |
-----------------+------------------- | |
April Fools Day | April 01, 012 | |
Valentine's Day | February 14, 012 | |
(2 rows) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
UPDATE holidays SET colors = '{"red", "green"}' where name = 'Christmas Day'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT count(title) | |
FROM events | |
WHERE title LIKE '%Day%'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
ERROR: cannot update view "holidays" | |
HINT: You need an unconditional ON UPDATE DO INSTEAD rule or an INSTEAD OF UPDATE trigger. |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
INSERT INTO holidays (name, date, colors) | |
VALUES ('New Year Day', '2013-01-01 00:00:00', '{"white","orange"}'); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT extract(year from starts) as year, | |
extract(month from starts) as month, count(*) | |
FROM events | |
GROUP BY year, month; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
CREATE TEMPORARY TABLE month_count(month INT); | |
INSERT INTO month_count VALUES (1), (2), (3), (4), (5), (6), (7), (8),(9), (10), (11), (12); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
DROP TRIGGER logical_delete ON venues; | |
CREATE TRIGGER logical_delete | |
BEFORE DELETE ON venues | |
FOR EACH ROW EXECUTE PROCEDURE logical_delete(); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
INSERT INTO venues (name, postal_code, country_code) VALUES('My Home', '97205', 'us'); | |
DELETE FROM venues WHERE name = 'My Home'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
count | |
------- | |
3 | |
(1 row) |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT min(starts), max(ends) | |
FROM events INNER JOIN venues | |
ON events.venue_id = venues.venue_id | |
WHERE venues.name = 'Crystal Ballroom'; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT venue_id, count(*) | |
FROM events | |
GROUP BY venue_id | |
ORDER BY venue_id; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT DISTINCT venue_id FROM events GROUP BY venue_id; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT venue_id, count(*) | |
FROM events | |
GROUP BY venue_id | |
HAVING count(*) >= 2 AND venue_id IS NOT NULL; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
SELECT venue_id, count(*) | |
OVER (PARTITION BY venue_id) | |
FROM events | |
ORDER BY venue_id; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment