98 lines
3.2 KiB
PL/PgSQL
98 lines
3.2 KiB
PL/PgSQL
-- P5-1676
|
|
|
|
CREATE OR REPLACE FUNCTION public.getsafexmlpath(
|
|
val character varying,
|
|
with_root integer DEFAULT 0)
|
|
RETURNS character varying AS
|
|
$BODY$
|
|
BEGIN
|
|
IF with_root = 1 THEN
|
|
val = regexp_replace(val, '^\w+', 'root');
|
|
ELSIF with_root = 2 THEN
|
|
val = regexp_replace(val, '^\w+/', '');
|
|
END IF;
|
|
|
|
val = UNACCENT(val);
|
|
val = REGEXP_REPLACE(val, '[\., _]', '-', 'g');
|
|
val = REPLACE(val, '&', '-'); /* ampersand should always be blank but it gets fixed in below */
|
|
val = lower(val);
|
|
val = REGEXP_REPLACE(val, '-+', '-', 'g');
|
|
val = REGEXP_REPLACE(val, '[^\/\w\-]', '', 'g');
|
|
|
|
RETURN val;
|
|
|
|
END; $BODY$
|
|
LANGUAGE plpgsql VOLATILE
|
|
COST 100;
|
|
ALTER FUNCTION public.getsafexmlpath(character varying, integer)
|
|
OWNER TO photowall;
|
|
GRANT EXECUTE ON FUNCTION public.getsafexmlpath(character varying, integer) TO public;
|
|
GRANT EXECUTE ON FUNCTION public.getsafexmlpath(character varying, integer) TO photowall;
|
|
|
|
|
|
DROP MATERIALIZED VIEW public.v_esales_categorytree_i18n;
|
|
CREATE MATERIALIZED VIEW public.v_esales_categorytree_i18n AS
|
|
SELECT main.id,
|
|
main.parent_id,
|
|
main.locale_id,
|
|
main.locale,
|
|
main.name,
|
|
main.path,
|
|
main.is_leaf,
|
|
main.depth,
|
|
main.child_count,
|
|
main.lft,
|
|
main.rgt
|
|
FROM ( SELECT c.id,
|
|
( SELECT s.id
|
|
FROM categories s
|
|
WHERE s.lft < c.lft AND s.rgt > c.rgt
|
|
ORDER BY (s.rgt - c.rgt)
|
|
LIMIT 1) AS parent_id,
|
|
c.locale_id,
|
|
c.locale,
|
|
c.name,
|
|
( SELECT lower(array_to_string(array_agg(main_1.parent_name), '/'::text)) AS path
|
|
FROM ( SELECT node.id,
|
|
( SELECT category_texts.name
|
|
FROM category_texts
|
|
WHERE category_texts.locale_id = c.locale_id AND category_texts.category_id = parent.id) AS parent_name
|
|
FROM categories node,
|
|
categories parent
|
|
WHERE node.lft >= parent.lft AND node.lft <= parent.rgt AND node.id = c.id
|
|
ORDER BY parent.lft) main_1
|
|
GROUP BY main_1.id) AS path,
|
|
CASE c.rgt - c.lft
|
|
WHEN 1 THEN 1
|
|
ELSE 0
|
|
END AS is_leaf,
|
|
( SELECT count(*) - 1
|
|
FROM categories node,
|
|
categories parent
|
|
WHERE node.lft >= parent.lft AND node.lft <= parent.rgt AND node.id = c.id) AS depth,
|
|
( SELECT count(*) - 1
|
|
FROM categories c1
|
|
WHERE c1.lft >= c.lft AND c1.lft <= c.rgt) AS child_count,
|
|
c.lft,
|
|
c.rgt
|
|
FROM ( SELECT c_1.id,
|
|
s.id AS locale_id,
|
|
s.value AS locale,
|
|
lower(s_t.name::text) AS name,
|
|
c_1.rgt,
|
|
c_1.lft
|
|
FROM locales s
|
|
JOIN category_texts s_t ON s.id = s_t.locale_id
|
|
JOIN categories c_1 ON s_t.category_id = c_1.id
|
|
ORDER BY s.id, c_1.lft) c) main
|
|
WITH DATA;
|
|
|
|
ALTER TABLE public.v_esales_categorytree_i18n
|
|
OWNER TO photowall;
|
|
|
|
CREATE UNIQUE INDEX v_esales_categorytree_i18n_id_locale_id
|
|
ON public.v_esales_categorytree_i18n
|
|
USING btree
|
|
(id, locale_id);
|
|
|