APPENDIX · POSTGRESQL LIVE DEMO · SLIDE P6
P6. VIEW Analytics Dashboard and Report on Purchases and Matches
Click Run—we'll access the game.v_player_summary view, which uses the game_readonly role
-- Запрашиваем витрину игроков (колонка email скрыта представлением!):
SELECT
player_id,
nickname,
COALESCE(clan_tag, '— без клана —') AS clan,
level,
purchases_cnt,
total_spent,
kills_cnt
FROM game.v_player_summary
ORDER BY total_spent DESC, kills_cnt DESC;
Summary of the Performance
• An analyst or intern with the game_readonly role executes a simple SELECT query on game.v_player_summary.
• They receive a ready-made summary of each player’s clans, purchases, total spending, and frags, without having access to the private email field in the game.players table.
• In Practice 5, this is exactly the kind of query that’s convenient to export to CSV using the \copy command!
Ready to export to CSV
All materials in the repository
Good luck with Practical Exercise 5!
Wrap this query in \copy (SELECT ...) TO 'report.csv' WITH CSV HEADER — and the report for the game designer is ready.
The complete script for the lab is located in the Lesson 5/dba/live_admin_pg.sql folder, and a detailed cheat sheet of commands is in Lesson 5/dba/appendix.md.
Deploy your schema from Exercise 2 in Docker, test it in DBeaver, configure the three roles, and perform a practice pg_restore.