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
2006/10/25, Richard Huxton <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, 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
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 mostappreciated.
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 LOOPEXECUTE 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.