Keyo Keyo - 2 months ago 15
SQL Question

Mapping values without a table

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

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.