#335 SQL +19
		CREATE OR REPLACE FUNCTION melodious.query_flags(user_name varchar(32), chan_name varchar(32), igroup_name varchar(32), iflag_name varchar(32), flagcheck bool)
		RETURNS TABLE (
			group_holders jsonb [],
			flag_id int4,
			flag_name varchar(32),
			flag jsonb
		)
		LANGUAGE plpgsql
		AS $$
		DECLARE
			uid int4 := NULL;
			cid int4 := NULL;
			gid int4 := NULL;
		BEGIN
			IF user_name <> '' THEN
				SELECT id INTO uid FROM melodious.accounts WHERE username=user_name;
				IF uid IS NULL THEN
					RAISE EXCEPTION 'no such user';
				END IF;
			END IF;
			IF chan_name <> '' THEN
				SELECT id INTO cid FROM melodious.channels WHERE name=chan_name;
				IF cid IS NULL THEN
					RAISE EXCEPTION 'no such channel';
				END IF;
			END IF;
			IF igroup_name <> '' THEN
				SELECT id INTO gid FROM melodious.groups WHERE name=igroup_name;
				IF gid IS NULL THEN
					RAISE EXCEPTION 'go such group';
				END IF;
			END IF;

			IF igroup_name = '' THEN
				IF iflag_name = '' THEN
					IF user_name = '' AND chan_name = '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB((SELECT name FROM melodious.groups WHERE id=gh.group_id LIMIT 1)))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE (NOT flagcheck) OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
						GROUP BY gf.id, gf.name;
					ELSIF user_name <> '' AND chan_name <> '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB((SELECT name FROM melodious.groups WHERE id=gh.group_id LIMIT 1)))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE (gh.user_id = uid AND gh.channel_id = cid)
							 OR (flagcheck AND gh.user_id = uid AND gh.channel_id IS NULL)
							 OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id = cid)
							 OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
						GROUP BY gf.id, gf.name;
					ELSIF user_name <> '' AND chan_name = '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB((SELECT name FROM melodious.groups WHERE id=gh.group_id LIMIT 1)))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE (NOT flagcheck AND gh.user_id = uid)
							 OR (flagcheck AND gh.user_id = uid AND gh.channel_id IS NULL)
							 OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
						GROUP BY gf.id, gf.name;
					ELSIF user_name = '' AND chan_name <> '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB((SELECT name FROM melodious.groups WHERE id=gh.group_id LIMIT 1)))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE (NOT flagcheck AND gh.channel_id = cid)
							 OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id = cid)
							 OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
						GROUP BY gf.id, gf.name;
					END IF;
				ELSE
					IF user_name = '' AND chan_name = '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB((SELECT name FROM melodious.groups WHERE id=gh.group_id LIMIT 1)))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE (gf.name = iflag_name)
							AND (
								(NOT flagcheck)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name <> '' AND chan_name <> '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB((SELECT name FROM melodious.groups WHERE id=gh.group_id LIMIT 1)))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE gf.name = iflag_name
						  AND (
								(gh.user_id = uid AND gh.channel_id = cid)
							 	OR (flagcheck AND gh.user_id = uid AND gh.channel_id IS NULL)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id = cid)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name <> '' AND chan_name = '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB((SELECT name FROM melodious.groups WHERE id=gh.group_id LIMIT 1)))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE gf.name = iflag_name
							AND (
								(NOT flagcheck AND gh.user_id = uid)
								OR (flagcheck AND gh.user_id = uid AND gh.channel_id IS NULL)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name = '' AND chan_name <> '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB((SELECT name FROM melodious.groups WHERE id=gh.group_id LIMIT 1)))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE gf.name = iflag_name
							AND (
								(NOT flagcheck AND gh.channel_id = cid)
								OR (flagcheck AND gh.channel_id = cid AND gh.user_id IS NULL)
								OR (flagcheck AND gh.channel_id IS NULL AND gh.user_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					END IF;
				END IF;
			ELSE
				IF iflag_name = '' THEN
					IF user_name = '' AND chan_name = '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB(igroup_name))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE gh.group_id = gid
							AND (
								(NOT flagcheck)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name <> '' AND chan_name <> '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB(igroup_name))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE gh.group_id = gid
							AND (
								(gh.channel_id = cid AND gh.user_id = uid)
								OR (flagcheck AND gh.channel_id = cid AND gh.user_id IS NULL)
								OR (flagcheck AND gh.channel_id IS NULL AND gh.user_id = uid)
								OR (flagcheck AND gh.channel_id IS NULL AND gh.user_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name <> '' AND chan_name = '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB(igroup_name))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE gh.group_id = gid
							AND (
								(NOT flagcheck AND gh.user_id = uid)
								OR (flagcheck AND gh.user_id = uid AND gh.channel_id IS NULL)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name = '' AND chan_name <> '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB(igroup_name))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE gh.group_id = gid
							AND (
								(NOT flagcheck AND gh.channel_id = cid)
								OR (flagcheck AND gh.channel_id = cid AND gh.user_id IS NULL)
								OR (flagcheck AND gh.channel_id IS NULL AND gh.user_id IS NULL) 
							)
						GROUP BY gf.id, gf.name;
					END IF;
				ELSE
					IF user_name = '' AND chan_name = '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB(igroup_name))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						INNER JOIN melodious.groups g
						ON g.id = gh.group_id
						WHERE (gf.name = iflag_name AND gh.group_id = gid)
							AND (
								(NOT flagcheck)
								OR (flagcheck AND gh.channel_id IS NULL AND gh.user_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name <> '' AND chan_name <> '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB(igroup_name))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE (gf.name = iflag_name AND gh.group_id = gid)
							AND (
								(gh.user_id = uid AND gh.channel_id = cid)
								OR (flagcheck AND gh.user_id = uid AND gh.channel_id IS NULL)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id = cid)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name <> '' AND chan_name = '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB(igroup_name))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE (gh.group_id = gid AND gf.name = iflag_name)
							AND (
								(NOT flagcheck AND gh.user_id = uid)
								OR (flagcheck AND gh.user_id = uid AND gh.channel_id IS NULL)
								OR (flagcheck AND gh.user_id IS NULL AND gh.channel_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					ELSIF user_name = '' AND chan_name <> '' THEN
						RETURN QUERY SELECT
							ARRAY_AGG(JSONB_SET(ROW_TO_JSON(gh)::JSONB, '{group_name}'::TEXT[], TO_JSONB(igroup_name))) AS group_holders,
							gf.id flag_id,
							gf.name flag_name,
							gf.flag flag
						FROM melodious.group_holders gh
						INNER JOIN melodious.group_flags gf
						ON gh.group_id = gf.group_id
						WHERE (gf.name = iflag_name AND gh.group_id = gid)
							AND (
								(NOT flagcheck AND gh.channel_id = cid)
								OR (flagcheck AND gh.channel_id = cid AND gh.user_id IS NULL)
								OR (flagcheck AND gh.channel_id IS NULL AND gh.user_id IS NULL)
							)
						GROUP BY gf.id, gf.name;
					END IF;
				END IF;
			END IF;
		END;
		$$;
By Anonymous, 2019-02-05 15:36:52