473,836 Members | 1,452 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Hierarchical queries

Hello everybody!

Does someone know how to build hierarchical queries to the postgresql?

I have a table with tree in it (id, parent)
and need to find a way from any point of the tree to any other point.
And i would like to have a list of all steps from point A to point B
to make some changes on each step (this is required by the algorythm).

Here is an example:
treetable (where tree is stored):
id parent data
int4 int4 varchar(255)
0 0 root
1 0 root's chield 1
2 0 root's chield 2
3 1 root's chield 1 chield 1
4 1 root's chield 1 chield 2
5 2 root's chield 2 chield 1
6 2 root's chield 2 chield 2

And i want to get something like this:
start point "root's chield 2 chield 2"
finish "root's chield 1 chield 1"

And the result i need:
id parent data
6 2 root's chield 2 chield 2
2 0 root's chield 2
0 0 root
1 0 root's chield 1
4 1 root's chield 1 chield 2

i know that it is possible in Oracle but what about postgres?

Best regards,
Anton Nikiforov
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 12 '05
13 5911

On Sat, 10 Jan 2004, Cornelia Boenigk wrote:
I have a table with two fields, d1 timestamp and dur smallint.
d1 is the starting date and dur is the duration. From this two fields
I want to generate future dates for the whole table.


I'd suggest using something like:
d1 + dur * interval '1 month'
rather than attempting to do it via text.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #11
Hi Stephan

Thank you
d1 + dur * interval '1 month'

works ;-)

Regards
Conni

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Nov 12 '05 #12
Thanks Joe,
But this function is not giving a path from one element to other, it
is just truncating the tree beginning from the start element, but it
is not rotating the whole tree making starting element a tree's root.

JC> See contrib/tablefunc for a function called connectby().

Regards,
Anton
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #13
Hello Everybody!
Now i did what i was requesting :) One night with a computer :))
Many-many thanks to all of you :)
Below is script to create tables and function to get a path through a
tree. It is not a beautiful thing, but it is working :)
Maybe you could give me some optimization hints? :)
And maybe you could help me with the bug: when i'm calling this
function twice in a single connection i'm getting error
SELECT id, treetable.data FROM gettree(6,7) where id=treetable.id ;
ERROR: relation with OID 45041919 does not exist
CONTEXT: PL/pgSQL function "gettree" line 18 at for over select rows
Do you have any idea how to deal with it?

Best regards,
Anton

treefunc-0.0.sql file follows
=============== ===============
-- This table is made for feature caching abilities of my function. If
-- a tree big enough it will be a time consuming thing to sort it each
-- time it is needed. So i'm thinking about caching using timestamp.
DROP TABLE treeconfigtable CASCADE;
CREATE TABLE treeconfigtable (
date timestamp DEFAULT now() NOT NULL
);
INSERT INTO treeconfigtable (date) VALUES ('now');

-- This table is made only to format function's return
-- If there is a way not to use it - i'll appreciate any help
DROP TABLE pathtable CASCADE;
CREATE TABLE pathtable (
id INT4
);

-- Table that stores the tree itself
DROP SEQUENCE treesequence CASCADE;
CREATE SEQUENCE treesequence START 0 MINVALUE 0;
DROP TABLE treetable CASCADE;
CREATE TABLE treetable (
id INT4 NOT NULL PRIMARY KEY DEFAULT NEXTVAL('treese quence'),
parent INT4 NOT NULL DEFAULT 0,
data VARCHAR(255) NOT NULL,
blocked boolean DEFAULT FALSE
)
-- trigger that stores update time in treeconfigtable
DROP FUNCTION treeupdatedfunc tion ();
CREATE FUNCTION treeupdatedfunc tion () RETURNS TRIGGER AS '
BEGIN
UPDATE treeconfigtable SET date = now();
RETURN new;
END; '
LANGUAGE 'plpgsql';
CREATE TRIGGER treeupdatedtrig ger AFTER INSERT OR UPDATE OR DELETE ON treetable FOR EACH ROW EXECUTE PROCEDURE treeupdatedfunc tion();

-- This is inserts for testing, just a simple tree
INSERT INTO treetable (parent,data) VALUES (0,'root');
INSERT INTO treetable (parent,data) VALUES (0,'Chield1');
INSERT INTO treetable (parent,data) VALUES (1,'Chield1Chie ld1');
INSERT INTO treetable (parent,data) VALUES (0,'Chield2');
INSERT INTO treetable (parent,data) VALUES (3,'Chield2Chie ld2');

INSERT INTO treetable (parent,data) VALUES (4,'Ch2Ch2Ch1') ;
INSERT INTO treetable (parent,data) VALUES (4,'Ch2Ch2Ch2') ;

INSERT INTO treetable (parent,data) VALUES (2,'Ch1Ch1Ch1') ;
INSERT INTO treetable (parent,data) VALUES (2,'Ch1Ch1Ch2') ;

-- This is a main function that takes two arguments
-- ID of element FROM
-- ID of element TO
-- and rotating tree making TO element the root element.
CREATE OR REPLACE FUNCTION gettree (INT4, INT4) RETURNS SETOF pathtable AS '
DECLARE
temp RECORD;
buf INT4 := 0;
buf_record RECORD;
temp_id INT4 := 0;
record_id INT4 := 0;
record_parent INT4 := 0;
i INT4 := 0;
path RECORD;
BEGIN
CREATE TEMPORARY TABLE temptable AS SELECT * FROM treetable;
CREATE TEMPORARY TABLE tempidtable (id INT4);

-- We should start from the destination object id;
buf = $2;

-- And first of all we should fill buffer with at least one value.
FOR temp IN SELECT * FROM temptable WHERE (id = $2 OR parent = $2) AND blocked = FALSE LOOP
IF temp.id = $2 THEN
INSERT INTO tempidtable (id) VALUES (temp.parent);
temp_id = temp.id;
temp.id = temp.parent;
temp.parent = temp_id;
record_id = temp.parent;
record_parent = temp.id;
UPDATE temptable SET id = temp.id, parent = temp.parent, blocked = TRUE WHERE id = record_id AND parent = record_parent AND blocked = FALSE;
ELSE
INSERT INTO tempidtable (id) VALUES (temp.id);
UPDATE temptable SET id = temp.id, parent = temp.parent, blocked = TRUE WHERE id = temp.id AND parent = temp.parent AND blocked = FALSE;
END IF;
END LOOP;

-- And then we should continue sorting and rotating a tree to get
-- succseeded
LOOP
FOR buf_record IN SELECT id FROM tempidtable LOOP
FOR temp IN SELECT * FROM temptable WHERE (id = buf_record.id OR parent = buf_record.id) AND blocked = FALSE LOOP
IF temp.id = buf_record.id THEN
INSERT INTO tempidtable (id) VALUES (temp.parent);
temp_id = temp.id;
temp.id = temp.parent;
temp.parent = temp_id;
record_id = temp.parent;
record_parent = temp.id;
UPDATE temptable SET id = temp.id, parent = temp.parent, blocked = TRUE WHERE id = record_id AND parent = record_parent AND blocked = FALSE;
ELSE
INSERT INTO tempidtable (id) VALUES (temp.id);
UPDATE temptable SET id = temp.id, parent = temp.parent, blocked = TRUE WHERE id = temp.id AND parent = temp.parent AND blocked = FALSE;
END IF;
END LOOP;
DELETE FROM tempidtable WHERE id=buf_record.i d;
END LOOP;

-- Here we are checking if something left in the buffer
-- If nothing - just exit this loop
SELECT INTO temp * FROM tempidtable LIMIT 1;
IF NOT FOUND THEN
EXIT;
END IF;
END LOOP;
-- Now lets print the path from start to the end
SELECT INTO path * from pathtable;
buf = $1;
LOOP
path.id = buf;
RETURN NEXT path;
IF i = 0 THEN
i=1;
SELECT INTO temp * from temptable where id=buf;

ELSE
SELECT INTO temp * from temptable where id=buf AND blocked = TRUE;
END IF;
UPDATE temptable SET blocked = FALSE WHERE id = temp.id AND parent = temp.parent AND blocked = TRUE;
IF FOUND THEN

buf = temp.parent;
ELSE
EXIT;
END IF;
END LOOP;

-- How we do not need temp tables anymore
DROP TABLE tempidtable;
DROP TABLE temptable;
-- And lets finish procedure output :)
RETURN NULL;
END; '
LANGUAGE 'plpgsql';

=============== ===============
Now select from the function like this:
SELECT id, treetable.data FROM gettree(8,5) where id=treetable.id ;
And you should get a path (treetable.data added only for
visualization)
id | data
----+----------------
8 | Ch1Ch1Ch2
2 | Chield1Chield1
1 | Chield1
0 | root
3 | Chield2
4 | Chield2Chield2
5 | Ch2Ch2Ch1
(7 rows)

SELECT id, treetable.data FROM gettree(6,7) where id=treetable.id ;
id | data
----+----------------
6 | Ch2Ch2Ch2
4 | Chield2Chield2
3 | Chield2
0 | root
1 | Chield1
2 | Chield1Chield1
7 | Ch1Ch1Ch1
(7 rows)

ANlr> Hello everybody!

ANlr> Does someone know how to build hierarchical queries to the postgresql?

ANlr> I have a table with tree in it (id, parent)
ANlr> and need to find a way from any point of the tree to any other point.
ANlr> And i would like to have a list of all steps from point A to point B
ANlr> to make some changes on each step (this is required by the algorythm).

ANlr> Here is an example:
ANlr> treetable (where tree is stored):
ANlr> id parent data
ANlr> int4 int4 varchar(255)
ANlr> 0 0 root
ANlr> 1 0 root's chield 1
ANlr> 2 0 root's chield 2
ANlr> 3 1 root's chield 1 chield 1
ANlr> 4 1 root's chield 1 chield 2
ANlr> 5 2 root's chield 2 chield 1
ANlr> 6 2 root's chield 2 chield 2

ANlr> And i want to get something like this:
ANlr> start point "root's chield 2 chield 2"
ANlr> finish "root's chield 1 chield 1"

ANlr> And the result i need:
ANlr> id parent data
ANlr> 6 2 root's chield 2 chield 2
ANlr> 2 0 root's chield 2
ANlr> 0 0 root
ANlr> 1 0 root's chield 1
ANlr> 4 1 root's chield 1 chield 2

ANlr> i know that it is possible in Oracle but what about postgres?

ANlr> Best regards,
ANlr> Anton Nikiforov
ANlr> ---------------------------(end of broadcast)---------------------------
ANlr> TIP 7: don't forget to increase your free space map settings
,
IT ""

.: +7 095 7814200
: +7 095 7814201
Mail: An************* @loteco.ru
Web: www.loteco.ru
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 22 '05 #14

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
2048
by: Reimar Bauer | last post by:
Hi all, I would like to use a hierarchical group oriented encryption. Is there something implemented or did you know something I could use? For explanaition. If you have a large building there are several keys available. Each person has a key to open his/her room. Probably this key is able to open rooms of the group of this person.
1
3686
by: ALEX KLEIN | last post by:
I want to use fabricated hierarchical recordset in VB6 using ADO. I wrote code like dim rs as adodb.recordest set rs=new adodb.recordest rs.fields.append "a1",adChar,30 Then in loop I put rs.addnew rs("a1")=... re.update when I associated this with hierarchical flexgrid I saw what I expected. On
0
4124
by: Mike N. | last post by:
Hello to all: First let me apologize for the length of this question, I've made an attempt to include as much information as is needed to help with the question. I am having problems putting together a query to pull out an alternative hierarchical view of my data. The database is implemented under SQL Sever 2000 and I am writing the front end using VB.Net and ADO.net. The following is the portion of my database structure that I am...
3
5591
by: Cludia Morgado | last post by:
Hello! Oracle has the option with the SQL CONECT BY statement to run through a hierarchical database with a single SQl-statement: <!--SQL SELECT ms_id,ms_parent FROM messages CONNECT BY PRIOR ms_id = ms_parent START WITH ms_id = 1 --> Result-set (example): ms_id parent_id 1 1.1 1 1.1.1 1.1 1.1.2 1.1 1.1.3 1.1 1.2 1 1.2.1 1.2
4
2612
by: Daisy | last post by:
Let's say I've got a forum, where users can be moderators of each forum. Tables look like this: USER -------- user_key name FORUM
5
2259
by: clintonG | last post by:
I'm looking for documentation and would not turn my nose up to any code from anybody who thinks they are good at the design of an algorythm that can be used to generated a hierarchical relational data model. What? A Yahoo-like drill-down menu that is a series of categories and nested categories is a hierarchical relational data model. An example can be seen at but the review of the query string values strongly indicates
3
2047
by: Bennett Haselton | last post by:
I want to display a hierarchical listing of items from a database table, where, say, each row in the table has an "ID" field and a "parent_id" field giving the ID of its parent (NULL if it's at the top level of the hierarchy) -- like message posts and their replies. Is there a built-in way to do this, or a generally accepted simplest way? My first idea was to create a user control like HierarchicalListing that contains a Repeater, and...
12
5826
by: Steve | last post by:
I have been studying the Adjacency List Model as a means of achieving a folder structure in a project I am working on. Started with the excellent article by Gijs Van Tulder http://www.sitepoint.com/article/hierarchical-data-database My database has this basic structure: Id FolderName
30
2374
by: Vadim Tropashko | last post by:
Reposting with more clarification (as Jan asked). Suppose I have a BNFgrammar and a source text parsed into a tree. How would I query an identifier declaration? All the XQuery tutorials (where I went to gather some ideas) start with simpleminded examples like browsing all the descendants of / bookstore/book (and the bookstore XML design is such wrong idea to boot).
0
10546
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10588
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10254
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7790
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6978
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5823
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4448
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4013
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3112
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.