Re: Difficulty passing in an array of values to EXECUTE

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