Annotation: policy.get_menu

Information: policy.get_menu

Implementation: policy.get_menu

CREATE OR REPLACE FUNCTION policy.get_menu(user_id_ integer, office_id_ integer, culture_ text)
 RETURNS TABLE(menu_id integer, menu_text character varying, url character varying, menu_code character varying, level smallint, parent_menu_id integer)
 LANGUAGE plpgsql
AS $function$
    DECLARE culture_exists boolean = false;
BEGIN
    IF EXISTS(SELECT * FROM core.menu_locale WHERE culture=$3) THEN
        culture_exists := true;
    END IF;

    IF culture_exists THEN
        RETURN QUERY 
        SELECT
            core.menus.menu_id,
            core.menu_locale.menu_text,
            core.menus.url,
            core.menus.menu_code,
            core.menus.level,
            core.menus.parent_menu_id   
        FROM core.menus
        INNER JOIN policy.menu_access
        ON core.menus.menu_id = policy.menu_access.menu_id
        INNER JOIN core.menu_locale
        ON core.menus.menu_id = core.menu_locale.menu_id
        WHERE policy.menu_access.user_id=$1
        AND policy.menu_access.office_id=$2
        AND core.menu_locale.culture=$3;
    ELSE
        RETURN QUERY 
        SELECT
            core.menus.menu_id,
            core.menus.menu_text,
            core.menus.url,
            core.menus.menu_code,
            core.menus.level,
            core.menus.parent_menu_id   
        FROM core.menus
        INNER JOIN policy.menu_access
        ON core.menus.menu_id = policy.menu_access.menu_id
        WHERE policy.menu_access.user_id=$1
        AND policy.menu_access.office_id=$2;
    END IF;

END
$function$