Postgresql – Basic trigger in PL/pgSQL, to flip a boolean field if another field has text

plpgsqlpostgresqltrigger

I want a BOOLEAN field to be set to TRUE when a sibling TEXT field is set to a non-empty string. Vice versa, the BOOLEAN is set to FALSE when TEXT field is set to empty string.

The following function is running, but fails to flip the BOOLEAN field.

CREATE OR REPLACE FUNCTION set_flags_on_recipe_trigger_function_()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$BODY$

DECLARE

BEGIN

    IF (TG_TABLE_NAME = 'recipe_') THEN

        IF (TG_OP = 'INSERT') OR (TG_OP = 'UPDATE') THEN
            BEGIN
                NEW.is_baked_ = ( length( NEW.baking_instructions_ ) > 0 );
                NEW.is_roasted_ = ( length( NEW.roasting_instructions_ ) > 0 );
            END;
        END IF;

        RETURN NEW;

    ELSE
        RAISE EXCEPTION 'This trigger is being used on the wrong table.';

    END IF;

     /* Should never reach this point. Branching in code above should always reach a call to RETURN. */
    RAISE EXCEPTION 'Unexpectedly reached the bottom of this function without calling RETURN.';

END;

$BODY$;

Is this kind of question about programming PL/pgSQL appropriate to this site? Or should I be posting this to StackOverflow?

Best Answer

Your trigger function can be improved:

CREATE OR REPLACE FUNCTION trg_insup_bef_recipe()
  RETURNS TRIGGER AS
$func$
BEGIN
IF TG_TABLE_NAME = 'recipe_' THEN
   IF TG_OP IN ('INSERT', 'UPDATE') THEN
      NEW.is_baked_   := NEW.baking_instructions_ <> '';
      NEW.is_roasted_ := NEW.roasting_instructions_ <> '';
   END IF;
   RETURN NEW;
ELSE
    RAISE EXCEPTION 'This trigger is being used on the wrong table.';
END IF;
END
$func$ LANGUAGE plpgsql;

Major points

  • Do not use a separate code block without need. Removed the spurious BEGIN .. END.

  • NEW.baking_instructions_ <> '' is a 100% identical drop-in replacement for
    length( NEW.baking_instructions_ ) > 0. Just shorter and faster.

  • The plpgsql assignment operator is :=, not =.

  • Removed multiple parentheses to demonstrate they are just noise.

Simplify

Given the trigger definition in your answer (which should be in your question), you can further simplify the trigger function.

  • If your trigger function is not intended to be generic but for a certain trigger on a certain table (which is the normal case) I suggest to reflect that in the function name instead of adding code and error messages to the function body. Much cheaper and cleaner (IMHO), but that's a matter of taste and style:

CREATE OR REPLACE FUNCTION trg_insup_bef_recipe()
  RETURNS TRIGGER AS
$func$
BEGIN
NEW.is_baked_   := NEW.baking_instructions_ <> '';
NEW.is_roasted_ := NEW.roasting_instructions_ <> '';
END
$func$ LANGUAGE plpgsql;

And since this trigger only has work to do when any of the involved columns have been changed, you only need to trigger it in those cases:

CREATE TRIGGER insup_bef_recipe
BEFORE INSERT
OR UPDATE OF is_baked_, baking_instructions_, is_roasted_, roasting_instructions_
ON recipe_
FOR EACH ROW EXECUTE PROCEDURE trg_insup_bef_recipe();

Simplify further

Do you really need those flags materialized as additional columns? Could be completely (and more reliably) replaced with simple expressions in your queries:

baking_instructions_ <> '' AS is_baked_

Then you don't need a trigger at all. You could create a view with that expression. Or even "generated field". Detailed instructions:
Store common query as column? Computed / calculated columns in PostgreSQL

Related Question