Mapping values without a table

I need to use a map to assign a particular开发者_如何学C 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.

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.

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.

I would suggest that you create a temporary table and populate it with your data. Then you can link the tables and convert the data. While this might seem like a lot of work for one SQL statement and there might be quicker ways, this will set you up nicely for when you are ready to create a separate table. You can then just remove the temp table statement and INSERT statements and just change the table name you are linking on from the temp table to the newly-created table.

The only other fairly clean solution that I see would be to convert the data instead of using an If statement. For example, if the year 2000 is the only one that is a letter, you could do one if statement for if it is Y (and convert it) and another that is if it isn't Y, then the year is 2000 + year_code. If your years match up like that, it would be a fairly simple way of doing things for now.

I am partial to Object mapping, which can be accomplished via JSON.

You can ignore the CTE (with). I only included that so this can be tested standalone, without a year_code column.

WITH ref AS (SELECT '1' AS year_code)
  -- This is the only part you need
  SELECT COALESCE(
    (
      (json_build_object(
        'Y', 2000,
        '1', 2001,
        '2', 2001
      )::jsonb
    )->>(year_code::text))::integer,
    2000
  )
  -- END of the only part you need ;)
FROM ref;
-- RETURNS 2001