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;
