postgresql - Replacing pixel value by lookup table using PostGIS?

I was wondering if it is possible to write a PostgreSQL statement to replace the value of each pixel in an input PostGIS-raster with a value obtained from a look up table.

For example, suppose I have a binary raster bin_rast, and a look up table lookup as follows:

key   val
------------
0     -999
1     100

I wanted to replace every 0 pixel with -999, and every 1 pixel with 100.

How can I write such statement(s)?

---

This is what eventually worked for me, per @John's answer (with minor changes).

DROP TABLE IF EXISTS lookup;

CREATE TEMP TABLE lookup 
AS 
SELECT *
FROM (
   VALUES (0,-99999::numeric), (1,100::numeric)
) AS t (key, val);

UPDATE my_rast SET rast = 
   ST_Reclass(rast, 
       ROW(1, (SELECT string_agg(concat('[',key,'-',key,']:', val), ',') FROM lookup), '16BSI', 0)::reclassarg)

Answer

You are looking for ST_Reclass. There are three types of function signature, essentially differing on how you pass the reclassargs, which is essentially a textual encoding for mapping input to output pixel ranges.

UPDATE bin_rast SET reclass_rast = ST_Reclass(bin_rast, 1, '0:-999, 1:100', '8BUI', 0) 
WHERE rid = 1;

where obviously you will need to put in your own values for pixel type, band number, no value, raster names, and reclass_rast simply represents a new raster column (rather than updating in place).

If you want to actually do this from a lookup table dynamically, then you can use string concatenation along with a ::reglassargs cast, eg,

SELECT string_agg(concat(key,':', val)::text, ',') FROM lookup;

which will produce 0:-999,1:100. Putting that all together,

UPDATE bin_rast SET reclass_rast = 
   ST_Reclass(bin_rast, 1, 
    (select concat('''',string_agg(concat(key,':', val)::text, ','), '''') 
     FROM lookup)::regclassexpr, 
    '8BUI', 0) 
WHERE rid = 1;

If you don't like all the apostrophe escaping '''', look at the quote_ident function, but I have always done it this way.