Loop in function does not work as expected

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}