循环功能无法正常工作 [英] Loop in function does not work as expected

问题描述

使用PostgreSQL 9.0.4

Using PostgreSQL 9.0.4

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

Below is a very similar structure of my table:

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

有关我可以翻译的姓名的任何问题.

Any questions about the names I can translate ..

该功能的概念是什么?我首先检查临时表是否已经存在(执行)，并在发生异常时创建一个临时表.

What is the idea of the function? I first check if the temporary table already exists (perform), and when the exception occurs I create a temporary table.

然后，我将数组中的每个元素都用于获取部门的id和master_fk.如果搜索成功(检查id是否为null，则甚至没有必要)，我将id插入临时表并开始新的循环.

Then I take each element in the array and use it to fetch the id and master_fk of a department. If the search is successful (check if id is not null, it is even unnecessary) I insert the id in the temporary table and start a new loop.

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

The second loop is intended to get all parents of that department which was previously found by performing the previous steps (ie, pick a department and insert it into the temporary table).

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

At the end of the second loop returns to the first. When this one ends I return bigint[] refers to what was recorded in the temporary table.

我的问题是该函数向我返回了我提供的相同列表.我在做什么错了?

My problem is that the function returns me the same list I provide. What am I doing wrong?

推荐答案

有很多 我会做不同的事情，并且效果很好.

There is a lot I would do differently, and to great effect.

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

Starting with the table definition and naming conventions. These are mostly just opinions:

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
);

要点

• 您确定需要 部门?在这个星球上几乎没有那么多.一个简单的 serial 就足够了.

  Major points

  • Are you sure you need a bigserial for departments? There are hardly that many on this planet. A plain serial should suffice.

    我几乎从未将 character varying 与长度限制.与其他一些RDBMS不同，使用限制不会带来任何性能提升.如果您确实需要实施最大长度，请添加一个CHECK约束.我只使用 text ，主要是

    I hardly ever use character varying with a length restriction. Unlike with some other RDBMS there is no performance gain whatsoever by using a restriction. Add a CHECK constraint if you really need to enforce a maximum length. I just use text, mostly and save myself the trouble.

    我建议使用一种命名约定，即外键列与引用的列共享名称，因此master_id代替master_fk，依此类推.还允许在联接中使用USING.

    I suggest a naming convention where the foreign key column shares the name with the referenced column, so master_id instead of master_fk, etc. Also allows to use USING in joins.

    我很少使用非描述性的列名id.在这里使用dept_id代替.

    And I rarely use the non-descriptive column name id. Using dept_id instead here.

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

    It can be largely simplified to:

    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的语法已经过时，并且变质.请改用函数参数.

      • ALIAS FOR $1 is outdated syntax and discouraged. Use function parameters instead.

        VARIADIC 参数使调用更加方便.相关:

        The VARIADIC parameter makes it more convenient to call. Related:

        • 在单个参数中传递多个值

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

        You don't need EXECUTE for queries without dynamic elements. Nothing to gain here.

        您无需异常处理即可创建表.在手册此处:

        You don't need exception handling to create a table. Quoting the manual here:

        提示:包含EXCEPTION子句的块要多得多 进入和退出比没有一个障碍物要昂贵.因此，不要 无需使用EXCEPTION.

        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 or later has CREATE TEMP TABLE IF NOT EXISTS. I use a workaround for 9.0 to conditionally create the temp table.

        Postgres 9.1还提供 FOREACH遍历数组.

        Postgres 9.1 also offer FOREACH to loop through an arrays.

        所有这些，这真是令人讨厌:您并不需要很多.

        All that said, here comes the bummer: you don't need most of this.

        即使在Postgres 9.0中， 递归CTE 使此操作更加简单:

        Even in Postgres 9.0, a recursive CTE makes this a whole lot simpler:

        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;
        

        同一个电话.

        与解释密切相关的答案:

        Closely related answer with explanation:

        • 树结构和递归

        SQL Fiddle演示了两者.

        这篇关于循环功能无法正常工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！