SHARE
TWEET

Untitled

a guest Feb 22nd, 2019 67 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION fn_category_get_childs_v2(id_pai integer)
  2. RETURNS integer[] AS
  3. $BODY$
  4.  
  5. DECLARE
  6.  
  7. ids_filhos integer array;
  8.  
  9.  
  10. BEGIN
  11.  
  12. SELECT array (
  13.  
  14. SELECT category_id FROM category WHERE category_id IN (
  15. (WITH RECURSIVE parent AS
  16. (
  17.     SELECT category_id , parent_id  from category WHERE category_id = id_pai
  18.     UNION ALL
  19.     SELECT t.category_id , t.parent_id FROM parent
  20.     INNER JOIN category t ON parent.category_id =  t.parent_id
  21. )
  22.  
  23. SELECT category_id FROM  parent
  24. WHERE category_id <> id_pai
  25. ) )
  26.  
  27.  
  28.  ) into ids_filhos;
  29.  
  30. return ids_filhos;
  31.  
  32. END;
  33.    
  34. select *
  35. from teste1_elements
  36. where category_id in (select * from fn_category_get_childs_v2(12))
  37.    
  38. select *
  39. from teste1_elements
  40. where category_id=any(select * from fn_category_get_childs_v2(12)))
  41.    
  42. ERROR:  operator does not exist: integer = integer[]
  43. LINE 1: select * from teste1_elements where category_id in (select *...
  44.                                                     ^
  45. HINT:  No operator matches the given name and argument type(s). You might need to add explicit type casts.
  46.    
  47. SELECT * from fn_category_get_childs_v2(12)
  48.    
  49. '{30,32,34,20,19,18,17,16,15,14}'
  50.    
  51. select *
  52. from teste1_elements
  53. where category_id = any(fn_category_get_childs_v2(12))
  54.    
  55. WITH RECURSIVE children AS (
  56.     SELECT category_id
  57.     FROM   category
  58.     WHERE  parent_id = id_pai
  59.  
  60.     UNION ALL
  61.     SELECT c.category_id
  62.     FROM   children ch
  63.     JOIN   category c ON c.parent_id = ch.category_id
  64.     )
  65. SELECT *
  66. FROM   children;
  67.    
  68. CREATE OR REPLACE FUNCTION f_cat_children(_id_pai int, OUT category_id int)
  69.   RETURNS SETOF int LANGUAGE SQL AS
  70. $func$
  71.    WITH RECURSIVE children AS (
  72.       SELECT c.category_id
  73.       FROM   category c
  74.       WHERE  c.parent_id = $1
  75.  
  76.       UNION ALL
  77.       SELECT c.category_id
  78.       FROM   children ch
  79.       JOIN   category c ON c.parent_id = ch.category_id
  80.       )
  81.    SELECT *
  82.    FROM   children;
  83. $func$;
  84.    
  85. SELECT t.*
  86. FROM   teste1_elements t
  87. JOIN   f_cat_children(12) USING (category_id)
  88.    
  89. WITH RECURSIVE children AS (
  90.     SELECT category_id
  91.     FROM   category
  92.     WHERE  parent_id = id_pai
  93.  
  94.     UNION ALL
  95.     SELECT c.category_id
  96.     FROM   children ch
  97.     JOIN   category c ON c.parent_id = ch.category_id
  98.     )
  99. SELECT t.*
  100. FROM   children
  101. JOIN   teste1_elements t USING (category_id);
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top