Rreturn ONLY rows that match ALL values from an array parameter

Question

I have 2 tables.

Table1 "ads":

id	description	status
1	Ad number 1	active
2	Ad number 2	active

Table 2 "ads_features":

id	ad	feature
1	1	feature 1
2	1	feature 2

I need a SQL function that will return values from 1 and add new column with values from Table 2. But should return only values if ALL items from function parameter "features" (type array) are found in Table 2. Here is what I have so far:

CREATE OR REPLACE FUNCTION filter_ads(features text[] default NULL)
RETURNS table (
    id uuid,
    description text,
    status text,
    ads_features text[]
)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY
    SELECT
        ads.*,
        ARRAY(
            SELECT featuresTable.feature
            FROM ads_features featuresTable
            WHERE featuresTable.ad = ads.id
        ) AS ads_features
    FROM ads
    WHERE
        (ads.status = 'active') AND
        features IS NULL OR COALESCE(features, '{}'::text[]) = '{}'::text[] OR EXISTS(
            SELECT 1
            FROM ads_features featuresTable
            WHERE (featuresTable.ad = ads.id) AND
            (featuresTable.feature = ANY(features))
        );
END;
$$;

The problem is that if I pass "[feature 1, feature 3]" as parameter, function will still return ad with ID 1, as ANY operator is satisfied. But I want to return records when ONLY ALL array items are found in table 2. If I use ALL operator it does not work at all. I get zero records.

Expected results:

Scenario 1:

select * from filter_ads(ARRAY(“feature 1”, “feature 2”))

id	description	status	ads_features
1	Ad number 1	active	[feature 1, feature 2]

Scenario 2:

select * from filter_ads(ARRAY(“feature 1”, “feature 3”))

No records found

Basically this is part of a filter function where user will select among many check boxes in UI and records that match all conditions will be shown.

Answer 1

feature contains only one value. You need an array of all features for that ad. group by ad and aggregate all of its features into an array with array_agg.

Then use having to constrain the aggregate and @> to check if one array contains the other.

select
  ads.*,
  array_agg(feature) as features
from ads
join ads_features
  on ads.id = ads_features.ad
where ads.status = 'active'
group by ads.id
having array_agg(feature) @> array['feature2', 'feature1']

Demonstration.