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

From: rawi <only4com(at)web(dot)de>
To: pgsql-sql(at)postgresql(dot)org
Subject: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable
Date: 2009-08-27 11:59:25
Message-ID: 25167538.post@talk.nabble.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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 g
UNION 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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message rawi 2009-08-27 14:16:49 resolved: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable
Previous Message Jorge Godoy 2009-08-27 11:25:49 Re: Howto automatically define collumn names for a function result.