Обсуждение: 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

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.