1

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'

0

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;
  • Dear Erwin, Your first proposal is simple and works great for me. The second is perfect too. Thank you very much! Stan – StanYan Sep 9 '16 at 19:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.