Is there a CASE LIKE in pl/pgsql?

Question

I'm trying to write a dynamic after trigger function that does an update based on the TG_TABLE_NAME, but it seems that plpgsql's CASE cannot do "LIKE". Is it called differently in plpgsql, or impossible?

If not possible, my second idea would be to do a SUBSTR, or are there better ways?

CREATE OR REPLACE FUNCTION upd_sys_table()
RETURNS TRIGGER AS $$
DECLARE
    lm BIGINT := (EXTRACT(epoch FROM timezone('UTC', now()))*1000)::bigint;
BEGIN
    CASE TG_TABLE_NAME
        WHEN  LIKE 'candy\_%' THEN
        BEGIN
            UPDATE sys_table SET last_modified = lm, modified_by = NEW.modified_by WHERE sys_table_name = 'xvw_candy';
        END;
        WHEN  LIKE 'fruit\_%' THEN
        BEGIN
            UPDATE sys_table SET last_modified = lm, modified_by = NEW.modified_by WHERE sys_table_name = 'xvw_fruit';
        END;
    ELSE
        RAISE NOTICE 'No match';
    END CASE;
RETURN NEW;

END;
$$ LANGUAGE plpgsql;

This simply updates a record in a table called sys_table, which keeps track of a last-modified date for a group of tables. The group of tables are prefixed with either candy_ and fruit_ (yes, I made that up).

Erwin Brandstetter · Answer 1 · 2021-03-07T23:42:49.410

As was pointed out, you got the CASE syntax wrong.

You also don't need sub-blocks, parameters, multiple distinct UPDATE statements or even the (broken) PL/pgSQL CASE construct. Just an SQL CASE expression in the WHERE clause of the UPDATE:

CREATE OR REPLACE FUNCTION upd_sys_table()
  RETURNS trigger
  LANGUAGE plpgsql AS
$func$
BEGIN
   UPDATE sys_table
   SET    last_modified = EXTRACT(epoch FROM timezone('UTC', now())) * 1000
        , modified_by   = NEW.modified_by
   WHERE  sys_table_name = CASE WHEN TG_TABLE_NAME LIKE 'candy\_%' THEN 'xvw_candy'
                                WHEN TG_TABLE_NAME LIKE 'fruit\_%' THEN 'xvw_fruit'
                           END;
IF NOT FOUND THEN
      RAISE NOTICE 'No match';
   END IF;
RETURN NEW;
END
$func$;

Subtle difference: this assumes that rows for 'xvw_candy' and 'xvw_fruit' always exist in sys_table. Else, there is an additional path to NOT FOUND.

Don't confuse SQL CASE with PL/pgSQL CASE. Same keyword, different language, subtly distinct syntax. Among other things, SQL CASE does not require an ELSE branch and defaults to NULL.

If the use case is as simple as your example suggests, you can even just look at the leading 5 characters and use the simpler "searched" CASE like you had in mind:

WHERE  sys_table_name = CASE left(TG_TABLE_NAME, 5)
                           WHEN 'candy' THEN 'xvw_candy'
                           WHEN 'fruit' THEN 'xvw_fruit'
                        END;

Or even just:

WHERE  sys_table_name = 'xvw_' || left(TG_TABLE_NAME, 5)

See:

Issue with composite type in an UPSERT function

Aside: it's typically better to store an actual timestamptz instead of an epoch as bigint. See:

How to best store a timestamp in PostgreSQL?

Thanks, I'm using your insights to further streamline my code, much appreciated. As for the timestamp, it's a bigint, because the application this DB is talking to selects from tables using an epoch number; why would it be better to store the epoch as a timestamptz? — svenema, Mar 06 '21 at 12:07
@svenema: timestamptz and bigint are both 8-byte integer quantities internally, with the same storage characteristics and basically the same performance characteristics. The bigint representation is meaningless to the human eye and lacks helpful sanity checks on input. Typically you store the result of now() and friends, which is cheaper without cast to bigint. And for all other date/time operations you need to cast to timestamp(tz) anyway. Typically, there is no good reason to use the inappropriate data type bigint. — Erwin Brandstetter, Mar 07 '21 at 18:06

score 1 · Answer 2 · answered Mar 05 '21 at 18:39

1

You’re using the searched syntax:

case a when ‘value’ then ... else ... end

whereas you should be using the expression syntax:

case when a like ‘...’ then ... else ... end

answered Mar 05 '21 at 18:39

Colin 't Hart

9,323
15
35
43

score 0 · Accepted Answer · answered Mar 05 '21 at 18:39

Yes there is, you have to put the TG_TABLE_NAMEnot behind the case, you have to put it in front of every LIKE

CREATE OR REPLACE FUNCTION upd_sys_table()
RETURNS TRIGGER AS $$
DECLARE
    lm BIGINT := (EXTRACT(epoch FROM timezone('UTC', now()))*1000)::bigint;
BEGIN
    CASE 
        WHEN  TG_TABLE_NAME LIKE 'candy\_%' THEN
        BEGIN
            UPDATE sys_table SET last_modified = lm, modified_by = NEW.modified_by WHERE sys_table_name = 'xvw_candy';
        END;
        WHEN  TG_TABLE_NAME LIKE 'fruit\_%' THEN
        BEGIN
            UPDATE sys_table SET last_modified = lm, modified_by = NEW.modified_by WHERE sys_table_name = 'xvw_fruit';
        END;
    ELSE
        RAISE NOTICE 'No match';
    END CASE;
RETURN NEW;

END;
$$ LANGUAGE plpgsql;

✓

db<>fiddle here

Is there a CASE LIKE in pl/pgsql?

3 Answers3