Re: Hierarchical queries - Mailing list pgsql-general

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('treesequence'),
        parent  INT4            NOT NULL DEFAULT 0,
        data    VARCHAR(255)    NOT NULL,
        blocked boolean         DEFAULT FALSE
)
-- trigger that stores update time in treeconfigtable
DROP FUNCTION treeupdatedfunction ();
CREATE FUNCTION treeupdatedfunction () RETURNS TRIGGER AS '
BEGIN
        UPDATE treeconfigtable SET date = now();
        RETURN new;
END; '
LANGUAGE 'plpgsql';
CREATE TRIGGER treeupdatedtrigger AFTER INSERT OR UPDATE OR DELETE ON treetable FOR EACH ROW EXECUTE PROCEDURE
treeupdatedfunction();

-- 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,'Chield1Chield1');
INSERT INTO treetable (parent,data) VALUES (0,'Chield2');
INSERT INTO treetable (parent,data) VALUES (3,'Chield2Chield2');

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
ANDparent = 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.id;
                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: Anton.Nikiforov@loteco.ru
Web: www.loteco.ru