Search Postgresql Archives

Problem with WITH RECURSIVE

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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
	


-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux