SELECT name, cp.p_count FROM catalog_category AS cc
INNER JOIN LATERAL (
SELECT cc.id AS id, SUM(products) AS p_count FROM (
SELECT cc.id AS parent_id, category_id, COUNT(id) AS products
FROM catalog_product
WHERE category_id IN (
SELECT id FROM catalog_category
WHERE lft <= cc.rght AND lft >= cc.lft AND tree_id = cc.tree_id)
GROUP BY category_id
) AS sub_cс
GROUP BY parent_id
) AS cp
USING(id) ORDER BY name;INSERT INTO Users(id, username, first_name, last_name)
VALUES ($1, $2, $3, $4)
ON CONFLICT
DO UPDATE SET
username=EXCLUDED.username
RETURNING confirmcreate sequence shared_sequence;
create table production_data(id bigint default nextval('shared_sequence'), value text);
create table demo_data(id bigint default nextval('shared_sequence'), value text);