From: rawi <only4com@web.de>
To: pgsql-sql@postgresql.org
Subject: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable
Date: Thu, 27 Aug 2009 04:59:25 -0700 (PDT)
Message-ID: <25167538.post@talk.nabble.com> (raw)
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-tp25167538p2...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: only4com@web.de
Subject: Re: WITH RECURSIVE: ARRAY[id] All column datatypes must be hashable
In-Reply-To: <25167538.post@talk.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox