Mapping values without a table

Question

I need to use a map to assign a particular value to year based on the value year_code has. At the moment I have a large if statement which is obviously hard 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've been instructed to keep it all within the one postgres function for the sake of getting it done quickly. Later on I plan to store all this in tables.

So is there a way I can create a temporary map and select form it to get the value for the year. Really I just want to clean up this ugly code. Thanks.

Answer 1

Use a Common Table Expression (CTE) within your function will make it easy to replace the CTE with a base table later e.g.

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

Alternatively, a derived table:

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

Perhaps that later base table could be a calendar table.

Answer 2

The quick and dirty solution would be a big ugly CASE statement:

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;

I don't know if that's any better than a big ugly pile of IFs though.

You could use a temporary table but then you'd have to store the big ugly pile of data somewhere and you'd have to check if the temp table is already there and populate it if it isn't.

You say that you don't have hstore installed by you could fake it with a PostgreSQL array and a WHILE loop:

-- 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;

I guess it is a question of which flavor of ugly hack you want.