Loop in function does not work as expected

Question

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
)

And the function I created:

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.

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

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?

Erwin Brandstetter · Accepted Answer

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

Table definition

Starting with the table definition and naming conventions. Mostly just suggestions:

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

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

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

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 USING in joins.

And I avoid the non-descriptive column name "id". Using "dept_id" instead here.

PL/pgSQL function

It can be largely simplified to:

CREATE OR REPLACE FUNCTION f_retornar_plpgsql(lista_ini_depts VARIADIC int[])
  RETURNS int[]
  LANGUAGE plpgsql AS
$func$
DECLARE
   _row departamento;   -- %ROWTYPE is just noise
   _elem int;
BEGIN
   CREATE TEMP TABLE IF NOT EXISTS tbl_temp_dptos (  -- simpler since Postgres 9.1
     dept_id bigint NOT NULL
   )  ON COMMIT DELETE ROWS;

   FOREACH _elem IN ARRAY lista_ini_depts  -- simpler since Postgres 9.1
   LOOP
      SELECT *
      INTO   _row              -- since you defined _row as rowtype, * is best
      FROM   departamento
      WHERE  dept_id = _elem;
   
      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$;

Call:

SELECT f_retornar_plpgsql(2, 5);

Or:

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

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

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

Pass multiple values in single parameter

You don't need EXECUTE for queries without dynamic elements.

You don't need exception handling to create a table. Quoting the manual 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 or later has CREATE TEMP TABLE IF NOT EXISTS.

Postgres 9.1 also offer FOREACH to loop through an arrays.

All that said, you don't need most of this:

SQL function with rCTE

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[]
  LANGUAGE sql 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)    -- you made half an attempt to get distinct values
$func$;

Same call.

Related, with more explanation:

Tree Structure and Recursion

fiddle
_{Old sqlfiddle}

Loop in function does not work as expected

Answers (2)

Table definition

PL/pgSQL function

SQL function with rCTE

Related Questions