循环功能无法正常工作

admin

循环功能无法正常工作

sql

使用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 []引用临时表中记录的内容。

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

阅读 162

2021-06-07

共1个答案

admin

还有很多我会做出不同的，并且很大的影响。

表定义

从表定义和命名约定开始。这些主要只是意见：

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，主要是为了省掉麻烦。
我建议使用一种命名约定，其中外键列与引用的列共享名称，因此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是过时的语法，不鼓励使用。请改用功能参数。
该 VARIADIC 参数使调用更加方便。有关的：
- 在单个参数中传递多个值
- 您不需要EXECUTE没有动态元素的查询。在这里没有收获。
您不需要异常处理即可创建表。在这里引用手册：

提示：与没有EXCEPTION子句的块相比，包含子句的块的进入和退出成本要高得多。因此，请不要使用EXCEPTION而无需使用。

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

话虽如此，这真是令人 the舌： 您不需要大部分。

带rCTE的SQL函数

即使在Postgres 9.0中，
递归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;

2021-06-07