Difficulty passing in an array of values to EXECUTE SELECT statement

[Nick Rowlands <nick.rowlands@xxxxxxxxxxxxxxxx>]

Hi there,

I'm having trouble creating a function using plpgsql. I cannot pass the
array 'productids' to the ANY construct of the IN EXECUTE SELECT
statement. Any ideas on what I'm doing wrong would be most appreciated.
Here's the function:

CREATE OR REPLACE FUNCTION search_products(metalparam int4, stoneparam
int4, jewelleryparam text)
  RETURNS SETOF search_result AS
$BODY$DECLARE
    row RECORD;
    search_result search_result%ROWTYPE;
    productids integer[];
    filter_jewellery text := '';
BEGIN
   
    IF metalparam > 0 AND stoneparam > 0 THEN
        productids := ARRAY(SELECT product_id FROM product_options WHERE
option_id = metalparam INTERSECT SELECT product_id FROM product_options
WHERE option_id = stoneparam);
    ELSIF metalparam > 0 THEN
        productids := ARRAY(SELECT product_id FROM product_options WHERE
option_id = metalparam);
    ELSIF stoneparam > 0 THEN
        productids := ARRAY(SELECT product_id FROM product_options WHERE
option_id = stoneparam);
    END IF;

    IF jewelleryparam != '' THEN
        filter_jewellery := ' AND j.name LIKE ''%';
        filter_jewellery := filter_jewellery || jewelleryparam;
        filter_jewellery := filter_jewellery || '%''';
    END IF;
   
    FOR row IN EXECUTE 'SELECT p.id, sku, description, base_price,
j.name AS jname, r.name AS rname
    FROM products2 p
    INNER JOIN jewellery_types j ON j.id = p.jewellery_type
    INNER JOIN ranges r ON r.id = p.range_id
    WHERE p.id = ANY(productids)' || filter_jewellery LOOP

        search_result.id := row.id;
        search_result.sku := row.sku;
        search_result.description := row.description;
        search_result.price := row.base_price;
        search_result.jname := row.jname;
        search_result.rname := row.rname;

        RETURN NEXT search_result;

    END LOOP;

RETURN;

END;$BODY$
  LANGUAGE 'plpgsql' VOLATILE;
ALTER FUNCTION search_products(metalparam int4, stoneparam int4,
jewelleryparam text) OWNER TO shops;