PostgreSQL

Retrieving an hierarchical tree recursively with PL/PgSQL

From PostgreSQL 8.4 you can write WITH queries using the optional RECURSIVE modifier to make a query refer to its own output.

Ok, but if I’m using a previous version of PostgreSQL? What can I do? I had this same question and, after some research, I found a solution based on functions.

Below I’ll explain how the functions works.

To run this example, first you need a table like this:

CREATE TABLE area (
id bigint NOT NULL,
parent bigint,
name character varying(50) NOT NULL,
CONSTRAINT fk_area FOREIGN KEY (parent) REFERENCES area(id);
);

As you can see the table has a column that references to itself, the parent column. For the areas in the top of hierarchy, the parent column should have NULL value.

After you created the table you can insert some records in the area table:

INSERT INTO area VALUES (1, NULL, 'Area 1');
INSERT INTO area VALUES (2, 1, 'Area 1.1');
INSERT INTO area VALUES (3, 2, 'Area 1.1.1');
INSERT INTO area VALUES (4, 1, 'Area 1.2');

The first function I’ve written is here:

CREATE OR REPLACE FUNCTION area_children (area_id BIGINT, self BOOLEAN)
  RETURNS SETOF area AS
$BODY$
DECLARE
  r area%ROWTYPE;
BEGIN
  IF self THEN
    RETURN QUERY SELECT * FROM area WHERE id = area_id;
  END IF;
  FOR r IN SELECT * FROM area WHERE parent = area_id
  LOOP
    RETURN NEXT r;
    RETURN QUERY SELECT * FROM area_children(r.id, FALSE);
  END LOOP;
  RETURN;
END
$BODY$
LANGUAGE 'plpgsql';

The area_id parameter contains the parent area id, all children of this area in any level of the hierarchy will be retrieved. The second parameter, self, allows user to define if the first parameter’s area will be returned too. What this function does is:

If the current area should be returned, select its record:

IF self THEN
  RETURN QUERY SELECT * FROM area WHERE id = area_id;
END IF;

For each child of the current area, add it (NEXT r) to results and retrieves its children calling the function recursively.

FOR r IN SELECT * FROM area WHERE parent = area_id
LOOP
  RETURN NEXT r;
  RETURN QUERY SELECT * FROM area_children(r.id, FALSE);
END LOOP;

To call this function within a SELECT statement, you can execute this query, where 1 should be a parent area id for which you want the children:

SELECT * FROM area_children(1, FALSE);

Finally I’ve written a second function that overloads the first function to make the second parameter default value to false:

CREATE OR REPLACE FUNCTION area_children (area_id BIGINT)
  RETURNS SETOF area AS
$BODY$
BEGIN
  RETURN QUERY SELECT * FROM area_children(area_id, TRUE);
  RETURN;
END
$BODY$
LANGUAGE 'plpgsql';

You can insert an other main area in your table to check if the function is returning the expected results.

INSERT INTO area VALUES (5, NULL, 'Area 2');

If you run the following query, the Area 2 shouldn’t appear.

SELECT * FROM area_children(1);
Tip

 

If an error is raised when you try to create the functions, check if the plpgsql function is in pg_language table. If it’s not present, run the following query:

CREATE LANGUAGE plpgsql;

References:

Thanks to VRoy for the SQL Server example.

About these ads
Padrão

Um comentário sobre “Retrieving an hierarchical tree recursively with PL/PgSQL

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s