1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
| CREATE OR REPLACE FUNCTION get_definers_ltree(IN param_language integer, IN param_allowed_languages integer[], IN param_tag_id integer DEFAULT 0)
RETURNS TABLE(definer_id integer, definer_parent_id integer, definer_label character varying, definer_path ltree, translation_done boolean) AS
$BODY$
DECLARE
tag view_tags_definers%ROWTYPE;
BEGIN
RETURN QUERY SELECT definer.id, definer.parent_tag_id, definer.label, definer.path,
CASE WHEN count(tag_trans.id) < array_length(param_allowed_languages, 1) THEN false ELSE true END
FROM view_tags_definers definer
JOIN tags_translations tag_trans ON tag_trans.tag_id = definer.id AND tag_trans.language_id = ANY(param_allowed_languages)
WHERE definer.path ~ cast('*.' || param_tag_id::text || '.*' AS lquery) AND
((EXISTS (SELECT 1 FROM view_tags_definers WHERE id = definer.id AND language_id = param_language) AND definer.language_id = param_language) OR
(NOT EXISTS (SELECT 1 FROM view_tags_definers WHERE id = definer.id AND language_id = param_language) AND definer.language_id = 1))
GROUP BY definer.id, definer.parent_tag_id, definer.label, definer.path;
END
$BODY$
LANGUAGE plpgsql; |
Partager