cojack

DROP FUNCTION "insertCategory"( INT, VARCHAR );
DROP FUNCTION "deleteCategory"( SMALLINT );
DROP FUNCTION "moveCategoryDown"( SMALLINT );
DROP FUNCTION "moveCategoryUp"( SMALLINT );
DROP INDEX path_gist_idx ON "tree";
DROP INDEX path_idx ON "tree";
DROP TABLE "tree";

-- tabela
CREATE TABLE "tree" (
   "id"  SERIAL NOT NULL,
   "path"   LTREE,
   "sort" SMALLINT
);

-- indexy
CREATE INDEX path_gist_idx ON "tree" USING gist(path);
CREATE INDEX path_idx ON "tree" USING btree(path);

-- komentarze
COMMENT ON COLUMN "tree"."path" IS 'Scieżka drzewa';


-- podstawowe wartości
INSERT INTO "tree" ( "path", "sort" ) VALUES ('Top', 1);
INSERT INTO "tree" ( "path", "sort" ) VALUES ('Top.Science', 1);
INSERT INTO "tree" ( "path", "sort" ) VALUES ('Top.Science.Astronomy', 1);
INSERT INTO "tree" ( "path", "sort" ) VALUES ('Top.Science.Astronomy.Astrophysics', 1);
INSERT INTO "tree" ( "path", "sort" ) VALUES ('Top.Science.Astronomy.Cosmology', 2);
INSERT INTO "tree" ( "path", "sort" ) VALUES ('Top.Hobbies', 2);
INSERT INTO "tree" ( "path", "sort" ) VALUES ('Top.Hobbies.Amateurs_Astronomy', 1);
INSERT INTO "tree" ( "path", "sort" ) VALUES ('Top.Collections', 3);
INSERT INTO "tree" ( "path", "sort" ) VALUES ('Top.Collections.Pictures', 1);
INSERT INTO "tree" ( "path", "sort" ) VALUES ('Top.Collections.Pictures.Astronomy', 1);
INSERT INTO "tree" ( "path", "sort" ) VALUES ('Top.Collections.Pictures.Astronomy.Stars', 1);
INSERT INTO "tree" ( "path", "sort" ) VALUES ('Top.Collections.Pictures.Astronomy.Galaxies', 2);
INSERT INTO "tree" ( "path", "sort" ) VALUES ('Top.Collections.Pictures.Astronomy.Astronauts', 3);


CREATE FUNCTION "moveCategoryUp"( SMALLINT ) RETURNS BOOLEAN AS $$
   DECLARE
      "_id" ALIAS FOR $1;
   BEGIN

      RETURN TRUE;
   END;
$$ LANGUAGE 'plpgsql';


CREATE FUNCTION "moveCategoryDown"( SMALLINT ) RETURNS BOOLEAN AS $$
   DECLARE
      "_id" ALIAS FOR $1;
   BEGIN

      RETURN TRUE;
   END;
$$ LANGUAGE 'plpgsql';


CREATE FUNCTION "deleteCategory"( SMALLINT ) RETURNS BOOLEAN AS $$
   DECLARE
      "_id" ALIAS FOR $1;
   BEGIN

       RETURN TRUE;
   END;
$$ LANGUAGE 'plpgsql';

/**
 * Funkcja tworzy nową kategorię
 *
 * Wywołanie funkcji:
 * SELECT "insertCategory"( 2::smallint, 'Programowanie'::varchar);
 *
 * @param   smallint    id rodzica
 * @param   varchar     nazwa kategorii
 *
 * 10:12 <    depesz> cojack: i think you will need to sort on subltree(path, 0, nlevel(path)-2), sort;
 *
 * SELECT id, subpath(path, -1) as title, nlevel(path) as depth, sort FROM tree ORDER BY subltree(path, 0, nlevel(path)) ASC;
 *
 * To powy&#380;ej prawie dzia&#322;a ;p
 *
 */
CREATE FUNCTION "insertCategory"( INT, VARCHAR ) RETURNS BOOLEAN AS $$
   DECLARE
      "_parentIdCategory" ALIAS FOR $1;
      "_categoryName" ALIAS FOR $2;
   BEGIN

       INSERT INTO
          "tree" ( "path" )
      VALUES
         (
            (
               SELECT
                  "path"
               FROM
                  "tree"
               WHERE
                  "id" = "_parentIdCategory"::INT
            )
            ||
            "_categoryName"::VARCHAR
         );

      IF FOUND THEN
         RETURN TRUE;
      ELSE
         RETURN FALSE;
      END IF;

   END;
$$ LANGUAGE 'plpgsql';

 INSERT INTO tree (path,sort) VALUES ('Top.Science.Programing',2);
 INSERT INTO tree (path,sort) VALUES ('Top.Science.Chemia',3);
 INSERT INTO tree (path,sort) VALUES ('Top.Science.Fizyka',4);


ltree=> SELECT * FROM tree;
 id |                     path                      | sort
----+-----------------------------------------------+------
  1 | Top                                           |    1
  2 | Top.Science                                   |    1
  3 | Top.Science.Astronomy                         |    1
  4 | Top.Science.Astronomy.Astrophysics            |    1
  5 | Top.Science.Astronomy.Cosmology               |    2
  6 | Top.Hobbies                                   |    2
  7 | Top.Hobbies.Amateurs_Astronomy                |    1
  8 | Top.Collections                               |    3
  9 | Top.Collections.Pictures                      |    1
 10 | Top.Collections.Pictures.Astronomy            |    1
 11 | Top.Collections.Pictures.Astronomy.Stars      |    1
 12 | Top.Collections.Pictures.Astronomy.Galaxies   |    2
 13 | Top.Collections.Pictures.Astronomy.Astronauts |    3
 14 | Top.Science.Programing                        |    2
 15 | Top.Science.Chemia                            |    3
 16 | Top.Science.Fizyka                            |    4
(16 ROWS)

Inny SELECT:

ltree=> SELECT id, subpath(path, -1) AS title, nlevel(path) AS depth, sort FROM tree ORDER BY subltree(path, 0, nlevel(path)) ASC, sort ASC;
 id |       title        | depth | sort
----+--------------------+-------+------
  1 | Top                |     1 |    1
  8 | Collections        |     2 |    3
  9 | Pictures           |     3 |    1
 10 | Astronomy          |     4 |    1
 13 | Astronauts         |     5 |    3
 12 | Galaxies           |     5 |    2
 11 | Stars              |     5 |    1
  6 | Hobbies            |     2 |    2
  7 | Amateurs_Astronomy |     3 |    1
  2 | Science            |     2 |    1
  3 | Astronomy          |     3 |    1
  4 | Astrophysics       |     4 |    1
  5 | Cosmology          |     4 |    2
 15 | Chemia             |     3 |    3
 16 | Fizyka             |     3 |    4
 14 | Programing         |     3 |    2
(16 ROWS)


A ja chcia&#322;bym BY TO wygl&#261;da&#322;o tak:
 id |       title        | depth | sort
----+--------------------+-------+------
  1 | Top                |     1 |    1
  2 | Science            |     2 |    1
  3 | Astronomy          |     3 |    1
  4 | Astrophysics       |     4 |    1
  5 | Cosmology          |     4 |    2
 15 | Chemia             |     3 |    2
 16 | Fizyka             |     3 |    3
 14 | Programing         |     3 |    4
  6 | Hobbies            |     2 |    2
  7 | Amateurs_Astronomy |     3 |    1
  8 | Collections        |     2 |    3
  9 | Pictures           |     3 |    1
 10 | Astronomy          |     4 |    1
 11 | Stars              |     5 |    1
 12 | Galaxies           |     5 |    2
 13 | Astronauts         |     5 |    3

Dasz rad&#281;?