Hello
I try to build a SQL for isolating hole pedigrees/families form a table with
persons.
Each person may have father_id and mother_id set to their parents as foreign
keys on the same persons table.
I was inspired by
http://akretschmer.blogspot.com/2008/10/waiting-for-84.html and I tryed to
develop the idea further to isolate the hole family of a given person, not
only his direct parents or children.
Despite the terrible bloating of the SQL... it works as long as no
consanguinity will be encountered, else the SQL runs in an endless loop.
I tryed to apply the trick with "path and cycle" from the "Postgresql 8.4
Documentation"
<cite>
whether we have reached the same row again while following a particular path
of links. We add two
columns path and cycle to the loop-prone query:
WITH RECURSIVE search_graph(id, link, data, depth, path, cycle) AS ( SELECT g.id, g.link, g.data, 1,
ARRAY[g.id],false FROM graph gUNION ALL SELECT g.id, g.link, g.data, sg.depth + 1, path || g.id, g.id = ANY(path)
FROM graph g, search_graph sg WHERE g.id = sg.link AND NOT cycle
)
SELECT * FROM search_graph;
</cite>
However it doesn't works like this:
<cite>
WITH RECURSIVE person (id,name,father_id,mother_id,level,path,cycle) AS ( SELECT f.id, f.name, f.father_id,
f.mother_id,0, ARRAY[f.id], false FROM pedigree f
</cite>
I get:
<cite>
ERROR: could not implement recursive UNION
DETAIL: All column datatypes must be hashable.
********** Error **********
ERROR: could not implement recursive UNION
SQL state: 0A000
Detail: All column datatypes must be hashable.
</cite>
It works without the ARRAY...
I would very appreciate any help, then I couldn't find anything on the net
about "All column datatypes must be hashable", excepting the postgresql
sources...
Thanks
rawi
--
View this message in context:
http://www.nabble.com/WITH-RECURSIVE%3A-ARRAY-id--All-column-datatypes-must-be-hashable-tp25167538p25167538.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
