Return multiple IDs from a function and use the result in a query

Posted by NewK on Stack Overflow See other posts from Stack Overflow or by NewK
Published on 2012-09-10T16:43:16Z Indexed on 2012/09/11 21:38 UTC
Read the original article Hit count: 183

Filed under:
|
|
|

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

© Stack Overflow or respective owner

Related posts about sql

Related posts about postgresql