Hi all
I am running PostgreSQL 9.0.3 on Fedora 14.
I am trying to use WITH RECURSIVE on an adjacency list. It is mostly
working, but I have hit a snag.
CREATE TABLE departments (
row_id SERIAL PRIMARY KEY,
code VARCHAR NOT NULL
parent_id INT REFERENCES departments,
description VARCHAR NOT NULL);
I want to create a query that outputs the data in a 'nested' sequence, the
same sequence that a 'nested set' would produce.
The technique I am trying is to create a computed column called 'seq' - for
the anchor select, it consists of the root's 'code', and for each iteration
I append the next level's 'code', separated by '\'. At the end, I order by
'seq'.
Here is my attempt -
WITH RECURSIVE all_depts AS (
SELECT row_id, code, description, parent_id,
0 AS level, CAST(code AS VARCHAR) AS seq
FROM departments
WHERE code = 'root'
UNION ALL
SELECT a.row_id, a.code, a.description,
a.parent_id, b.level+1,
CAST(b.seq || '\' || a.code AS varchar) AS seq
FROM departments a, all_depts b
WHERE b.row_id = a.parent_id)
SELECT * FROM all_depts ORDER BY seq
I added the two 'CAST ... AS VARCHAR' in an attempt to fix the following
error, but it made no difference.
When I run it, this is the error message that appears -
==================
recursive query "all_depts" column 6 has type character varying(999) in
non-recursive term but type character varying overall
HINT: Cast the output of the non-recursive term to the correct type.
==================
As explained above, I tried adding a CAST, but it did not help.
Here are two additional snippets of information that may be of use -
1. I ran the query 'manually', by creating the tables 'fmtemp', 'fmwork',
and 'fminter', and following the sequence explained in the documentation.
This ran correctly without errors.
2. I tried exactly the same exercise using MS SQL SERVER 2005, with syntax
suitably adjusted. Before adding the CAST's, it also gave an error - "Types
don't match between the anchor and the recursive parts in column 'seq' ...".
After adding the CAST's, it ran correctly.
Any assistance will be appreciated.
Frank Millman
