PostgreSQL（简称PgSQL）是一种支持SQL99标准的开源关系型数据库，而MySQL仅支持SQL92标准，曾经看过一篇文章，标题大概是：“MySQL除了Insert、update、delecte、Select还能给我们提供什么？”。而PgSQL还支持触发器、存储过程、规则函数等等，这样我们的在程序开发中的很多数据逻辑（数据结构）都可以放在数据库中，而不需要用PHP来完成。

现在PgSQL支持在Win、Linux、FreeBSD、MacOS等大多数操作系统上安装使用，如果像MySQL那样不使用他的存储过程、触发器这些特性，和MySQL是一样的用法。网上也有人说PgSQL的速度比MySQL慢，我觉得这是不公平的比较，就像一辆0.8L的QQ在高速路上跑的速度跟一辆奥迪A4 1.8T在城市里跑的速度来进行比较一样根本不具备可比性，就像下面这样的递归应用，用PHP+MySQL来完成所花的时间，跟PHP+PgSQL来比较，数据量增加，MySQL所花的时间将成几何积数增长，而PgSQL始终变化就在一个范围内。

    比如一个无限分类的递归应用，用PHP来写，可能要一大篇，数据反复在数据库和程序之间交换，现在我们用PgSQL来实现，看看效果又是如何：


CODE:[Copy to clipboard]数据表modules
CREATE TABLE "public"."modules" (
"id" SERIAL,
"bom_id" INTEGER DEFAULT 0 NOT NULL,
"group_id" INTEGER NOT NULL,
"names" VARCHAR(20) NOT NULL,
"link" VARCHAR(50),
"taxis" INTEGER DEFAULT 0,
CONSTRAINT "modules_pkey" PRIMARY KEY("id"),
FOREIGN KEY ("group_id")
  REFERENCES "public"."groups"("id")
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
  NOT DEFERRABLE
) WITHOUT OIDS;



CODE:[Copy to clipboard]数据视图modules_view
CREATE OR REPLACE VIEW "public"."modules_view" (
  id,
  bom_id,
  taxis,
  names,
  link,
  group_id,
  group_name,
  group_link)
AS
SELECT m.id, m.bom_id, m.taxis, m."names", m.link, g.id AS group_id, g."names"
  AS group_name, g.link AS group_link
FROM modules m, groups g
WHERE (m.group_id = g.id);



CODE:[Copy to clipboard]通过id值查询父阶的过程函数
CREATE OR REPLACE FUNCTION "public"."bom_parent" (parent_table varchar, parent_id integer) RETURNS SETOF "public"."bom_ex" AS
$body$
/* 通过id值查询父阶 */
DECLARE
    ENDTREE INTEGER;
    NLVL INTEGER;
    REC INTEGER;
    RESULT "public"."bom_ex"%ROWTYPE;
    QUERY VARCHAR;

BEGIN
  ENDTREE:=0;
  NLVL:=1;

  IF ps_temp_table('STACK') THEN
    RAISE NOTICE 'TEMP TABLE "STACK" EXISTS!';
  ELSE
      CREATE TEMP TABLE STACK(
        id INTEGER,
        compositor INTEGER,
        sub_menu BOOLEAN,
        end_menu BOOLEAN
      ) WITHOUT OIDS ON COMMIT DELETE ROWS;
  END IF;

  IF ps_temp_table('STACK_TEMP') THEN
    RAISE NOTICE 'TEMP TABLE "STACK_TEMP" EXISTS!';
  ELSE
      CREATE TEMP TABLE STACK_TEMP(
        partno INTEGER,
        parent INTEGER,
        LVLS INTEGER,
        sub_menu BOOLEAN,
        end_menu BOOLEAN
      ) WITHOUT OIDS ON COMMIT DELETE ROWS;
  END IF;

  EXECUTE 'INSERT INTO STACK_TEMP(partno, parent, LVLS, sub_menu, end_menu)
        SELECT id, bom_id, 1, TRUE, TRUE FROM '||parent_table||'
        WHERE id='||parent_id;

  WHILE (ENDTREE=0) LOOP
      NLVL := NLVL + 1;

      EXECUTE 'INSERT INTO STACK_TEMP(partno, parent, LVLS, sub_menu, end_menu)
            SELECT A.id, A.bom_id, '||NLVL||',TRUE, TRUE
            FROM '||parent_table||' A, STACK_TEMP B
            WHERE A.id = B.parent AND A.id <> 0
            AND B.LVLS = '||NLVL||' -1';

      IF NOT EXISTS(SELECT partno FROM STACK_TEMP WHERE LVLS = NLVL) THEN
        UPDATE STACK_TEMP SET sub_menu = FALSE WHERE LVLS = NLVL - 1;
        ENDTREE := 1;
      END IF;
  END LOOP;

  INSERT INTO STACK(id, compositor, sub_menu, end_menu)
  SELECT partno, LVLS, sub_menu, end_menu
  FROM STACK_TEMP;

  QUERY := 'SELECT s.id AS id, s.compositor AS compositor, t.bom_id AS bom_id,
              t.taxis AS taxis, t.names AS names, t.link AS link,
              t.group_id AS group_id, t.group_name AS group_name,
              t.group_link AS group_link, s.sub_menu AS sub_menu,
              s.end_menu AS end_menu
            FROM STACK s, '||parent_table||' t
            WHERE s.id=t.id';
  FOR RESULT IN EXECUTE QUERY LOOP
    RETURN NEXT RESULT;
  END LOOP;
  RETURN;
END
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;



CODE:[Copy to clipboard]通过id值查询子阶的过程函数
CREATE OR REPLACE FUNCTION "public"."bom_part" (part_table varchar, part_id integer) RETURNS SETOF "public"."bom_ex" AS
$body$
/* 通过id值查询子阶 */
DECLARE
    LVL INTEGER;
    ROWCOUNT INTEGER;
    RESULT public.bom_ex%ROWTYPE;
    PARTMODE INTEGER;
    QUERY VARCHAR;
BEGIN
  LVL:=1;
  PARTMODE:=part_id;
  IF ps_temp_table('STACK') THEN
    RAISE NOTICE 'TEMP TABLE "STACK" EXISTS!';
  ELSE
      CREATE TEMP TABLE STACK(
        id INTEGER,
        compositor INTEGER,
        sub_menu BOOLEAN DEFAULT FALSE,
        end_menu BOOLEAN DEFAULT FALSE
      ) WITHOUT OIDS ON COMMIT DELETE ROWS;
  END IF;

  IF ps_temp_table('STACK_TEMP') THEN
    RAISE NOTICE 'TEMP TABLE "STACK_TEMP" EXISTS!';
  ELSE
      CREATE TEMP TABLE STACK_TEMP(
        partno INTEGER,
        LVLS INTEGER,
        taxis INTEGER
      ) WITHOUT OIDS ON COMMIT DELETE ROWS;
  END IF;

  EXECUTE 'INSERT INTO STACK_TEMP(partno, LVLS, taxis)
        SELECT id,1,taxis FROM '||part_table||'
        WHERE id = '||PARTMODE;

  WHILE (LVL>0) LOOP
      IF EXISTS(SELECT partno FROM STACK_TEMP WHERE LVLS=LVL) THEN

        SELECT partno INTO PARTMODE
        FROM STACK_TEMP
        WHERE LVLS=LVL
        ORDER BY taxis;

        INSERT INTO STACK(id,compositor)
        SELECT partno,LVL
        FROM STACK_TEMP
        WHERE LVLS=LVL
        AND partno=PARTMODE;

        DELETE FROM STACK_TEMP
        WHERE LVLS=LVL
        AND partno=PARTMODE;

        EXECUTE 'INSERT INTO STACK_TEMP(partno,LVLS,taxis)
              SELECT id,'||LVL+1||',taxis FROM '||part_table||'
              WHERE bom_id='||PARTMODE;

        GET DIAGNOSTICS ROWCOUNT = ROW_COUNT ;
        IF ROWCOUNT > 0 THEN
          UPDATE STACK SET sub_menu = true WHERE id=PARTMODE;
          LVL := LVL + 1;
        END IF;
      ELSE
          UPDATE STACK SET end_menu = true WHERE id=PARTMODE;
          LVL := LVL - 1;
      END IF;
  END LOOP;

  QUERY := 'SELECT s.id AS id, s.compositor AS compositor, t.bom_id AS bom_id,
              t.taxis AS taxis, t.names AS names, t.link AS link,
              t.group_id AS group_id, t.group_name AS group_name,
              t.group_link AS group_link, s.sub_menu AS sub_menu,
              s.end_menu AS end_menu
          FROM STACK s, '||part_table||' t
          WHERE s.id=t.id';
  FOR RESULT IN EXECUTE QUERY LOOP
      RETURN NEXT RESULT;
  END LOOP;
  RETURN;
END
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;



CODE:[Copy to clipboard]数据
INSERT INTO modules (id, bom_id, group_id, "names", link, taxis) VALUES (0, -1, 0, '', NULL, 0);
INSERT INTO modules (id, bom_id, group_id, "names", link, taxis) VALUES (1, 0, 1, '系统设定', '', 8);
INSERT INTO modules (id, bom_id, group_id, "names", link, taxis) VALUES (2, 0, 1, '系统用户', '', 9);
INSERT INTO modules (id, bom_id, group_id, "names", link, taxis) VALUES (3, 2, 4, '系统用户管理', 'Users', 1);
INSERT INTO modules (id, bom_id, group_id, "names", link, taxis) VALUES (4, 1, 1, '后台模块管理', 'ModuleManage', 1);
INSERT INTO modules (id, bom_id, group_id, "names", link, taxis) VALUES (5, 2, 1, '系统用户组管理', 'Groups', 2);
INSERT INTO modules (id, bom_id, group_id, "names", link, taxis) VALUES (6, 2, 0, '退出注销', 'Logout', 4);
INSERT INTO modules (id, bom_id, group_id, "names", link, taxis) VALUES (7, 2, 5, '修改密码', 'UserPassword', 3);
INSERT INTO modules (id, bom_id, group_id, "names", link, taxis) VALUES (8, 1, 6, '版权信息管理', 'CopyRight', 2);
INSERT INTO modules (id, bom_id, group_id, "names", link, taxis) VALUES (10, 0, 0, '新闻文章', '', 1);
INSERT INTO modules (id, bom_id, group_id, "names", link, taxis) VALUES (11, 10, 5, '专辑文章管理', '', 3);
INSERT INTO modules (id, bom_id, group_id, "names", link, taxis) VALUES (12, 10, 4, '文章分类管理', 'ArticleClass', 1);
INSERT INTO modules (id, bom_id, group_id, "names", link, taxis) VALUES (13, 10, 5, '新闻文章管理', 'Article', 2);
INSERT INTO modules (id, bom_id, group_id, "names", link, taxis) VALUES (14, 0, 5, '软件下载', '', 2);
INSERT INTO modules (id, bom_id, group_id, "names", link, taxis) VALUES (15, 1, 4, '前台设定管理', 'WebsiteConf', 3);
INSERT INTO modules (id, bom_id, group_id, "names", link, taxis) VALUES (16, 2, 1, '网络站点管理', 'WebSite', 1);
在php、jsp、 asp 或者java这些程序中我们只用简单第发出一个命令，我们就可以或者一组递归显示的数据：


CODE:[Copy to clipboard]SELECT * FROM bom_part('modules_view', 0)
这样是不是很简单呢？