plpgsql function with "case when" to update column value [duplicate]

Question

I want to create a plpgsql function that will be called by a BEFORE INSERT trigger. This function is updating a column based on value from another column.

Here is the code:

CREATE OR REPLACE FUNCTION Xupd_cat_prop()
                           RETURNS TRIGGER
AS
$$ 
DECLARE categ_prop varchar;
BEGIN
    kateg_prop := CASE
        WHEN new.cat_tarif = 'domestic_1' OR new.cat_tarif = 'domestic_2' THEN new.class = 'DOM'
        WHEN new.cat_tarif = 'nondomestic_1' THEN new.class = 'NONDOM'
        WHEN new.cat_tarif = 'industry' THEN new.class = 'IND'
    END;
  RETURN new; 
END;
$$ 
LANGUAGE plpgsql;

But this doesn't work. I am using Postgres 16. The error is somewhere with the WHEN clause.

Pavel Stehule · Accepted Answer · 2024-11-08 07:09:03Z

3

You use SQL CASE statement. The WHEN clause can contains only expressions:

kateg_prop := CASE
        WHEN new.cat_tarif = 'domestic_1' OR new.cat_tarif = 'domestic_2' THEN 'DOM'
        WHEN new.cat_tarif = 'nondomestic_1' THEN 'NONDOM'
        WHEN new.cat_tarif = 'industry' THEN 'IND'
    END;

Although the syntax is similar, there are significant differences between SQL (functional) and PL/pgSQL (procedural) CASE:

SQL CASE:

 CASE var WHEN expr THEN expr [...] END

PLpgSQL CASE

  CASE var WHEN expr THEN plpgsql statement [...] END CASE

SQL CASE returns a value, PL/pgSQL CASE executes statements.

edited Nov 8, 2024 at 7:09

answered Nov 8, 2024 at 6:58

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

plpgsql function with "case when" to update column value [duplicate]

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related