This does the job:
CREATE MATERIALIZED VIEW speedy_materialized_view AS
WITH RECURSIVE tree AS (
SELECT id, parent_id, ARRAY[id] AS path
FROM gear_category
WHERE parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, path || c.id
FROM tree t
JOIN gear_category c ON c.parent_id = t.id
)
, tree_ct AS (
SELECT t.id, t.path, COALESCE(i.item_ct, 0) AS item_ct
FROM tree t
LEFT JOIN (
SELECT category_id AS id, count(*) AS item_ct
FROM gear_item
GROUP BY 1
) i USING (id)
)
SELECT t.id
, t.item_ct AS count_direct_child_items
, sum(t1.item_ct) AS count_recursive_child_items
FROM tree_ct t
LEFT JOIN tree_ct t1 ON t1.path[1:array_upper(t.path, 1)] = t.path
GROUP BY t.id, t.item_ct;
count_recursive_child_items
is counted separately for each category, so I am not convinced it's the fastest possible way for deep trees.
However, aggregate functions are not allowed in recursive CTEs.
Recursive Function
Strictly speaking, it's really iterative - but so is a "recursive" CTE.
You could build a function working with temp tables. You need to know your way around plpgsql or there is too much to explain.
CREATE OR REPLACE FUNCTION f_tree_ct()
RETURNS TABLE (id int, count_direct_child_items int, count_recursive_child_items int) AS
$func$
DECLARE
_lvl int;
BEGIN
-- basic table with added path and count
CREATE TEMP TABLE t1 AS
WITH RECURSIVE tree AS (
SELECT c.id, c.parent_id, '{}'::int[] AS path, 0 AS lvl
FROM gear_category c
WHERE c.parent_id IS NULL
UNION ALL
SELECT c.id, c.parent_id, path || c.parent_id, lvl + 1
FROM tree t
JOIN gear_category c ON c.parent_id = t.id
)
, tree_ct AS (
SELECT t.id, t.parent_id, t.path, t.lvl, COALESCE(i.item_ct, 0) AS item_ct
FROM tree t
LEFT JOIN (
SELECT i.category_id AS id, count(*)::int AS item_ct
FROM gear_item i
GROUP BY 1
) i USING (id)
)
TABLE tree_ct;
-- CREATE INDEX ON t1 (lvl); -- only for very deep trees
SELECT INTO _lvl max(lvl) FROM t1; -- identify max lvl to start bottom up
-- recursively aggregate each level in 2nd temp table
CREATE TEMP TABLE t2 AS
SELECT t1.id, t1.parent_id, t1.lvl
, t1.item_ct
, t1.item_ct AS sum_ct
FROM t1
WHERE t1.lvl = _lvl;
IF _lvl > 0 THEN
FOR i IN REVERSE _lvl .. 1 LOOP
INSERT INTO t2
SELECT t1.id, t1.parent_id, t1.lvl, t1.item_ct
, CASE WHEN t2.sum_ct IS NULL THEN t1.item_ct ELSE t1.item_ct + t2.sum_ct END
FROM t1
LEFT JOIN (
SELECT t2.parent_id AS id, sum(t2.sum_ct) AS sum_ct
FROM t2
WHERE t2.lvl = i
GROUP BY 1
) t2 USING (id)
WHERE t1.lvl = i - 1;
END LOOP;
END IF;
RETURN QUERY -- only requested columns, unsorted
SELECT t2.id, t2.item_ct, t2.sum_ct FROM t2;
DROP TABLE t1, t2; -- to allow repeated execution in one transaction
RETURN;
END
$func$ LANGUAGE plpgsql;
This cannot be included in a CREATE MATERIALIZED VIEW
statement because of the use of temporary tables. You could just create another (temp) table with it, acting as a manually maintained "materialized view":
CREATE TABLE speedy_materialized_view AS
SELECT * FROM f_tree_ct();
Alternatively you could TRUNCATE speedy_materialized_view
in the function and write to it directly. The function would RETURNS void
instead or you could return some meta-information like a row count ...
SQL Fiddle.
Aside:
Column aliases in the recursive term of a CTE are just for documentation since output column names are determined by the non-recursive term only.
Most common ...
In statistics, this is called mode.
Yes, PostgreSQL has an aggregate called mode
. I haven't messed with it in PostgreSQL.
Retrieves those not wanted...
You want the resulting set of all movies for a genre
minus the data set containing all movies rented by a customer for that genre
In PostgreSQL, you'll want to say EXCEPT
instead of MINUS
.
Best Answer
I think something like this should do it:
This should show any tables or views which are directly referenced by the view. If your view references other views, and you want to drill down to the base tables, you would need to apply this logic recursively (probably with a
RECURSIVE
CTE).pg_class.relkind IN ('r','f','p','v','m')
covers all of the table and view types as of Postgres 11, but the list may change in future releases. The possible values are covered in the docs.Many different kinds of dependencies are stored in
pg_depend
, and not all of them are relevant to your question. For example, the view'spg_rewrite
entry has a dependency on its ownpg_class
entry; thepg_depend.refobjid <> pg_rewrite.ev_class
constraint is there to filter this out. I may well have overlooked something else which ought to be excluded, so proceed with caution.