PostgreSQL – Function to Get Levels in Hierarchy

plpgsqlpostgresql

I need a function which returns description of level from cascade (hierarchy) tables.

CREATE TABLE level_1(
 id_1 SERIAL,
 description_1 text
);

CREATE TABLE level_2(
 id_2 SERIAL,
 description_2 text
);

CREATE TABLE level_3(
 id_3 int4 SERIAL,
 description_3 text
);

CREATE TABLE levels(
 id_levels int4 SERIAL,
 id_1 int4,
 id_2 int4,
 id_3 int4
);

I need this function:

SELECT f_get_level('Level 1', 10);

'Level 1' .. which level I want
10 .. value of id_levels

With this result:

description_1

The table levels is cross table between the others. Example:
Level 1 – Site
Level 2 – Sectors in Site
Level 3 – Lines in Sector

Levels: Level 1 – Level 2 – Level 3

id1  - Site (S1) - Sector (NULL) - Line (NULL) 
id3  - Site (S1) - Sector (Sec1) - Line (NULL) 
id4  - Site (S1) - Sector (Sec1) - Line (L1) 
id5  - Site (S1) - Sector (Sec1) - Line (L2) 
id6  - Site (S1) - Sector (Sec1) - Line (L3) 
id7  - Site (S1) - Sector (Sec2) - Line (NULL)
id8  - Site (S1) - Sector (Sec2) - Line (L1) 
id9  - Site (S1) - Sector (Sec2) - Line (L2) 
id10 - Site (S2) - Sector (NULL) - Line (NULL) 
id11 - Site (S2) - Sector (Sec1) - Line (NULL)

I need a function where parameters are id and Level.
Examples:

Select f_get_level('Sector', 5)  -- where 5 is id5

Result: 'Sec1' 

Select f_get_level('Site', 11)   -- where 11 is id11

Result: 'S2' 

Select f_get_level('Line', 6)    -- where 6 is id6

Result: 'L3' 

Select f_get_level('Sector', 6)   -- where 6 is id6

Result: 'Sec1'

Best Answer

One simple way would be correlated subqueries in a CASE expression of a single query:

CREATE OR REPLACE FUNCTION f_get_level(_level text, _id_levels int)
  RETURNS text AS
$func$
SELECT CASE _level
          WHEN 'Level 1' THEN (SELECT description_1 FROM level_1 WHERE id_1 = l.id_1)
          WHEN 'Level 2' THEN (SELECT description_2 FROM level_2 WHERE id_2 = l.id_2)
          WHEN 'Level 3' THEN (SELECT description_3 FROM level_3 WHERE id_3 = l.id_3)
          ELSE 'Unexpected input for _level!'
       END
FROM   levels  l
WHERE  l.id_levels = _id_levels
$func$  LANGUAGE sql STABLE;

Call (exactly as requested):

SELECT f_get_level('Level 1', 10);

Another option would be a PL/pgSQL function with separate CASE (not SQL CASE!) branches and an OUT parameter ...

CREATE OR REPLACE FUNCTION f_get_level2(_level text, _id_levels int, OUT _descr text) AS
$func$
BEGIN
   CASE _level
   WHEN 'Level 1' THEN
      SELECT description_1 
      FROM   levels  l
      JOIN   level_1 l1 USING (id_1)
      WHERE  l.id_levels = _id_levels
      INTO   _descr;
   WHEN 'Level 2' THEN
      SELECT description_2 
      FROM   levels  l
      JOIN   level_2 l2 USING (id_2)
      WHERE  l.id_levels = _id_levels
      INTO   _descr;
   WHEN 'Level 3' THEN
      SELECT description_3 
      FROM   levels  l
      JOIN   level_3 l3 USING (id_3)
      WHERE  l.id_levels = _id_levels
      INTO   _descr;
   ELSE
      RAISE EXCEPTION 'Unexpected input for _level: %!', _level;
   END CASE;
END
$func$  LANGUAGE plpgsql STABLE;
Related Question