22 lines
530 B
Plaintext
22 lines
530 B
Plaintext
|
-- View: eh_view
|
||
|
|
|
||
|
|
DROP VIEW IF EXISTS eh_view;
|
||
|
|
|
||
|
|
CREATE OR REPLACE VIEW eh_view AS
|
||
|
|
SELECT ep.episode_key,
|
||
|
|
ep.release_date,
|
||
|
|
ep.title,
|
||
|
|
ep.summary,
|
||
|
|
( SELECT string_agg(h2.host::text, '; '::text ORDER BY h2.host::text) AS string_agg
|
||
|
|
FROM hosts h2,
|
||
|
|
episodes_hosts_xref eh2
|
||
|
|
WHERE eh2.hosts_id = h2.id
|
||
|
|
GROUP BY eh2.episodes_id
|
||
|
|
HAVING eh2.episodes_id = ep.id) AS hosts
|
||
|
|
FROM episodes ep
|
||
|
|
GROUP BY ep.id
|
||
|
|
ORDER BY ep.id;
|
||
|
|
|
||
|
|
ALTER TABLE eh_view
|
||
|
|
OWNER TO hpradmin;
|