Geek asks and answers

Display values ​​without a table

I need to use a map to assign a specific value yearbased on a value year_code. At the moment, I have a big if statement, which is obviously difficult to maintain.

IF year_code = 'Y' THEN year := 2000; END IF;
IF year_code = '1' THEN year := 2001; END IF;
IF year_code = '2' THEN year := 2002; END IF;
-- and so on

The obvious solution would be to use a table and select a value, however, I was instructed to save all this within the framework of one postgres function in order to quickly execute it. Later I plan to store all this in tables.

So, I can create a temporary map and select it to get the value for the year. Actually, I just want to clear this ugly code. Thank.

+3
source share
3 answers

Common Table Expression (CTE) , CTE , .

WITH YearCodes (year_code, year) AS
     ( SELECT year_code, year
         FROM ( VALUES ( 'Y', 2000 ), 
                       ( '1', 2001 ), 
                       ( '2', 2002 ) ) 
              AS YearCodes ( year_code, year ) )
SELECT ...;

, :

SELECT *
  FROM ( VALUES ( 'Y', 2000 ), 
                ( '1', 2001 ), 
                ( '2', 2002 ) ) 
       AS YearCodes ( year_code, year )
       -- other stuff here;

, .

+10

. . SQL, , , , , . temp table INSERT , , temp .

, , - If. , 2000 , , if, Y ( ), , Y, 2000 + year_code, , .

+3

CASE:

CASE year_code
    WHEN 'Y' THEN year := 2000
    WHEN '1' THEN year := 2001
    -- ...
    ELSE year := NULL -- Or something else that makes sense or will
                      -- blow up so you know something is wrong.
END CASE;

, , IFs.

, -, , , , .

, hstore, , PostgreSQL WHILE:

-- Untested "off the top of my head" code
array := ARRAY['Y', '2000', '1', '2001', /* ... */ ];
i     := 1;
WHILE i <= array_length(array) LOOP
    IF year_code = array[i] THEN
        year := array[i + 1]::INTEGER;
        EXIT; -- Found it so bust out of the loop.
    ELSE
        i := i + 2;
    END IF;
END LOOP;

, , .

+3
source

More articles:


All Articles