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( '||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