Re: Difficulty passing in an array of values to EXECUTE

2006/10/25, Richard Huxton <dev(at)archonet(dot)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