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