[8.4] Where et sélection d'une autre row si et seulement si une première est introuvable

Ok, j'ai honte, c'était en effet très simple, il suffisait d'utiliser EXISTS... Je sentais la sous-requête, sans voir l'évident. Pour info, la fonction une fois bonne :

Code:

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;