WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable

Поиск
Список
Период
Сортировка
От rawi
Тема WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable
Дата
Msg-id 25167538.post@talk.nabble.com
обсуждение исходный текст
Ответы resolved: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable
Список pgsql-sql
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.



В списке pgsql-sql по дате отправления:

Предыдущее
От: Jorge Godoy
Дата:
Сообщение: Re: Howto automatically define collumn names for a function result.
Следующее
От: rawi
Дата:
Сообщение: resolved: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable