daily pastebin goal
33%
SHARE
TWEET

Untitled

a guest Feb 13th, 2018 48 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE TYPE CIR_TYPE AS
  2. (
  3.     ID integer,
  4.     path text,
  5.     cycle boolean
  6. );
  7.  
  8. CREATE OR REPLACE FUNCTION circular_ref() RETURNS setof CIR_TYPE AS $body$
  9.  
  10. DECLARE
  11.     r CIR_TYPE;
  12.  
  13. BEGIN
  14.     For r in WITH RECURSIVE graph(ID, path, cycle) AS (
  15.                 SELECT id AS id
  16.                      , ARRAY[parentid, id] AS path
  17.                      , (parentid = id) AS cycle    
  18.                 FROM  mytable
  19.  
  20.                 UNION ALL
  21.  
  22.                 SELECT d.id, d.parentid ||path, d.parentid = ANY(path)
  23.                 FROM   graph g
  24.                 JOIN   mytable d ON d.id = g.path[1]
  25.                 WHERE  NOT g.cycle
  26.              )
  27.              SELECT DISTINCT *
  28.              FROM   graph
  29.     Loop
  30.  
  31.         If r.cycle= TRUE then
  32.         Return NEXT r;
  33.         End if;
  34.  
  35.     End loop;
  36.  
  37. END;
  38. $body$ LANGUAGE plpgsql;
  39.    
  40. circular_ref
  41. ----------------------------
  42. (0,"{1,0,1}",t)
  43. (1,"{0,1,0}",t)
  44.    
  45. ID     |    Path
  46. ----       ------
  47. 0      |   1,0,1
  48. 1      |   0,1,0
  49.    
  50. SELECT * FROM circular_ref();
  51.    
  52. SELECT circular_ref();
  53.    
  54. SELECT id, path FROM circular_ref();
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