sql - 循环功能未按预期工作

Question

使用 PostgreSQL 9.0.4

下面是我的表的一个非常相似的结构:

CREATE TABLE departamento
(
  id bigserial NOT NULL,
  master_fk bigint,
  nome character varying(100) NOT NULL
  CONSTRAINT departamento_pkey PRIMARY KEY (id),
  CONSTRAINT departamento_master_fk_fkey FOREIGN KEY (master_fk)
      REFERENCES departamento (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)

以及我创建的函数:

CREATE OR REPLACE FUNCTION fn_retornar_dptos_ate_raiz(bigint[])
  RETURNS bigint[] AS
$BODY$
DECLARE
   lista_ini_dptos ALIAS FOR $1;
   dp_row departamento%ROWTYPE;
   dpto bigint;
   retorno_dptos bigint[];
BEGIN
   BEGIN
      PERFORM id FROM tbl_temp_dptos;
      EXCEPTION 
         WHEN undefined_table THEN
            EXECUTE 'CREATE TEMPORARY TABLE tbl_temp_dptos (id bigint NOT NULL) ON COMMIT DELETE ROWS';
   END;

   FOR i IN array_lower(lista_ini_dptos, 1)..array_upper(lista_ini_dptos, 1) LOOP
      SELECT id, master_fk INTO dp_row FROM departamento WHERE id=lista_ini_dptos[i];
      IF dp_row.id IS NOT NULL THEN
         EXECUTE 'INSERT INTO tbl_temp_dptos VALUES ($1)' USING dp_row.id;
         WHILE dp_row.master_fk IS NOT NULL LOOP
            dpto := dp_row.master_fk;
            SELECT id, master_fk INTO dp_row FROM departamento WHERE id=lista_ini_dptos[i];
            EXECUTE 'INSERT INTO tbl_temp_dptos VALUES ($1)' USING dp_row.id;
         END LOOP;
      END IF;
   END LOOP;

   RETURN ARRAY(SELECT id FROM tbl_temp_dptos);
END;
$BODY$
  LANGUAGE plpgsql VOLATILE

关于我可以翻译的名字的任何问题..

函数的思想是什么？我首先检查临时表是否已经存在(执行)，当异常发生时我创建一个临时表。

然后我获取数组中的每个元素并使用它来获取部门的 id 和 master_fk。如果搜索成功(检查id是否不为null，甚至不需要)我将id插入临时表并开始新的循环。

第二个循环旨在获取先前通过执行前面的步骤找到的该部门的所有父级(即，选择一个部门并将其插入到临时表中)。

在第二个循环结束时返回到第一个。当这个结束时，我返回 bigint[] 指的是临时表中记录的内容。

我的问题是该函数返回我提供的相同列表。我做错了什么？

Answer 1

很多我会做不同的事情，而且效果很好。

表定义

从表定义和命名约定开始。这些大多只是意见:

CREATE TEMP TABLE conta (conta_id bigint primary key, ...);

CREATE TEMP TABLE departamento (
   dept_id   serial PRIMARY KEY
 , master_id int REFERENCES departamento (dept_id)
 , conta_id  bigint NOT NULL REFERENCES conta (conta_id)
 , nome      text NOT NULL
);

要点

• 您确定需要部门的 bigserial 吗？这个星球上几乎没有那么多。一个普通的 serial 就足够了。

• 我几乎从不使用有长度限制的 character varying。与某些其他 RDBMS 不同，使用限制不会带来任何性能提升。如果确实需要强制执行最大长度，请添加 CHECK 约束。我只使用 text ，主要是 and save myself the trouble.

• 我建议一种命名约定，其中外键列与引用列共享名称，因此 master_id 而不是 master_fk 等。还允许使用USING 在联接中。

• 而且我很少使用非描述性列名 id。此处使用 dept_id。

PL/pgSQL 函数

它可以在很大程度上简化为:

CREATE OR REPLACE FUNCTION f_retornar_plpgsql(lista_ini_depts VARIADIC int[])
  RETURNS int[] AS
$func$
DECLARE
   _row departamento;                     -- %ROWTYPE is just noise
BEGIN

IF NOT EXISTS (                           -- simpler in 9.1+, see below
    SELECT FROM pg_catalog.pg_class
    WHERE  relnamespace = pg_my_temp_schema()
    AND    relname      = 'tbl_temp_dptos') THEN

   CREATE TEMP TABLE tbl_temp_dptos (dept_id bigint NOT NULL)
   ON COMMIT DELETE ROWS;
END IF;

FOR i IN array_lower(lista_ini_depts, 1)  -- simpler in 9.1+, see below
      .. array_upper(lista_ini_depts, 1) LOOP
   SELECT *  INTO _row                    -- since rowtype is defined, * is best
   FROM   departamento
   WHERE  dept_id = lista_ini_depts[i];

   CONTINUE WHEN NOT FOUND;

   INSERT INTO tbl_temp_dptos VALUES (_row.dept_id);

   LOOP
      SELECT *  INTO _row
      FROM   departamento
      WHERE  dept_id = _row.master_id;

      EXIT WHEN NOT FOUND;

      INSERT INTO tbl_temp_dptos
      SELECT _row.dept_id
      WHERE  NOT EXISTS (
         SELECT FROM tbl_temp_dptos
         WHERE dept_id =_row.dept_id);
   END LOOP;
END LOOP;

RETURN ARRAY(SELECT dept_id FROM tbl_temp_dptos);

END
$func$  LANGUAGE plpgsql;

调用:

SELECT f_retornar_plpgsql(2, 5);

或者:

SELECT f_retornar_plpgsql(VARIADIC '{2,5}');

• ALIAS FOR $1 是过时的语法和 discouraged 。请改用函数参数。

• VARIADIC参数调用更方便。相关:

  • Pass multiple values in single parameter

• 对于没有动态元素的查询，您不需要EXECUTE。在这里没有任何收获。

• 创建表不需要异常处理。引用手册 here :

  Tip: A block containing an EXCEPTION clause is significantly more expensive to enter and exit than a block without one. Therefore, don't use EXCEPTION without need.

• Postgres 9.1 或更高版本有 CREATE TEMP TABLE IF NOT EXISTS 。我使用 9.0 的解决方法有条件地创建临时表。

• Postgres 9.1 也提供 FOREACH to loop through an arrays 。

综上所述，不幸的是:您不需要其中的大部分内容。

带有 rCTE 的 SQL 函数

即使在 Postgres 9.0 中，recursive CTE 也使这变得更加简单:

CREATE OR REPLACE FUNCTION f_retornar_sql(lista_ini_depts VARIADIC int[])
  RETURNS int[] AS
$func$
WITH RECURSIVE cte AS (
   SELECT dept_id, master_id
   FROM   unnest($1) AS t(dept_id)
   JOIN   departamento USING (dept_id)

   UNION ALL
   SELECT d.dept_id, d.master_id
   FROM   cte
   JOIN   departamento d ON d.dept_id = cte.master_id
   )
SELECT ARRAY(SELECT DISTINCT dept_id FROM cte)    -- distinct values
$func$  LANGUAGE sql;

同样的调用。

与解释密切相关的答案:

• Tree Structure and Recursion

SQL Fiddle demonstrating both.