Thread: Difficulty passing in an array of values to EXECUTE SELECT statement
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;
Nick Rowlands wrote: > 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: > 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 EXECUTE takes a string, so you're giving it the word "productids" not the contents of the variable with that name. You'll need to generate a string containing comma-separated values (or the array definition) and use that. -- Richard Huxton Archonet Ltd
2006/10/25, Richard Huxton <dev@archonet.com>:
Nick Rowlands wrote:
> 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:
> 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( '||array_to_string(
productids,',')||') '|| filter_jewellery LOOP
EXECUTE takes a string, so you're giving it the word "productids" not
the contents of the variable with that name.
You'll need to generate a string containing comma-separated values (or
the array definition) and use that.
--
Richard Huxton
Archonet Ltd
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings
--
William Leite Araújo
William Leite Araújo wrote: > > 2006/10/25, Richard Huxton <dev@archonet.com <mailto:dev@archonet.com>>: > > Nick Rowlands wrote: > > 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: > > > FOR row IN EXECUTE 'SELECT p.id <http://p.id>, sku, > description, base_price, > > j.name <http://j.name> AS jname, r.name <http://r.name> AS rname > > FROM products2 p > > INNER JOIN jewellery_types j ON j.id <http://j.id> = > p.jewellery_type > > INNER JOIN ranges r ON r.id <http://r.id> = p.range_id > > > WHERE p.id <http://p.id> = ANY( > '|||array_to_string(|productids,',')||') '|| filter_jewellery LOOP > > > > > EXECUTE takes a string, so you're giving it the word "productids" not > the contents of the variable with that name. > > You'll need to generate a string containing comma-separated values (or > the array definition) and use that. > -- > Richard Huxton > Archonet Ltd > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > > > > -- > William Leite Araújo Fantastic. Thank you very much.