I have this function that returns me all children of a tree node:
CREATE OR REPLACE FUNCTION fn_category_get_childs_v2(id_pai integer)
RETURNS integer[] AS
$BODY$
DECLARE
ids_filhos integer array;
BEGIN
SELECT array (
SELECT category_id FROM category WHERE category_id IN (
(WITH RECURSIVE parent AS
(
SELECT category_id , parent_id from category WHERE category_id = id_pai
UNION ALL
SELECT t.category_id , t.parent_id FROM parent
INNER JOIN category t ON parent.category_id = t.parent_id
)
SELECT category_id FROM parent
WHERE category_id <> id_pai
) )
) into ids_filhos;
return ids_filhos;
END;
and I would like to use it in a select statement like this:
select *
from teste1_elements
where category_id in (select * from fn_category_get_childs_v2(12))
I've also tried this way with the same result:
select *
from teste1_elements
where category_id=any(select * from fn_category_get_childs_v2(12)))
But I get the following error:
ERROR: operator does not exist: integer = integer[]
LINE 1: select * from teste1_elements where category_id in (select *...
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
The function returns an integer array, is that the problem?
SELECT * from fn_category_get_childs_v2(12)
retrieves the following array (integer[]):
'{30,32,34,20,19,18,17,16,15,14}'
The function returns an integer array, is that the problem?
Yes. Typically, if you want to use where category_id in (select * from fn_category_get_childs_v2(12)) you'd want your function to return a set of rows, rather than an array. Something like RETURNS SETOF integer
The example in the manual may help:
It's also possible to use your existing function, however you'll have to use different syntax to compare against the array.
I think this should be closer:
select *
from teste1_elements
where category_id = any(fn_category_get_childs_v2(12))