Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
Minor tweak: That inner BEGIN END; is unnecessary. –  Basil Bourque Mar 19 at 3:14
    
Totally appropriate unless it's too basic. But add the [postgresql] tag for [plpgsql] questions. Most regulars only have the major tags on their radar. I, for one, missed the question ... –  Erwin Brandstetter Mar 29 at 2:02
    
As to the question: Your description contradicts the code example. You speak of one flag to be switched back and forth, you demonstrate two flags switched in one direction only. Which is it? –  Erwin Brandstetter Mar 29 at 2:03
    
@ErwinBrandstetter (a) Regarding one vs two flags, does it matter? I assume that once we arrive at the inside if the conditional testing code such as IF (TG_TABLE_NAME… we should put all the similar field-mutating code together. In this example that is a pair of Boolean fields. Is this approach wrong or ill-advised in trigger code? I am new to triggers. (b) I don't know what you mean by "one direction only". My logic using > should flip the Boolean TRUE and FALSE –  Basil Bourque Mar 29 at 17:36
    
(a) Doesn't matter much for the core of the question. But if I read a question and the description contradicts the example, uncertainty grows whether this is going to become confusing / frustrating and I tend to pass on the question. If I kept reading, because I recognized your name. (b) You are right. –  Erwin Brandstetter Mar 29 at 18:21
add comment

2 Answers 2

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

share|improve this answer
    
Amazing answer. That should provide an excellent lesson for trigger newbies. –  Basil Bourque Mar 29 at 18:00
    
As for needing an extra Boolean column in this scenario, you are correct in that I do not need one in theory. I am deliberately de-normalizing because of performance. I should have said so in question. I am collecting scientific readings every minute, stored in TEXT columns. That's a half million records a year, 30k each field. I need to find the few missing values caused by instrument data-collection glitches. I'm trying to avoid sequential reads of tens of gigs of data that would otherwise rarely be read. –  Basil Bourque Mar 29 at 18:06
1  
@BasilBourque: For the few missing values you might be interested in functional partial indexes instead of redundant flags. Several related answer can be found here. Ask a new question if those do not clarify the potential ... –  Erwin Brandstetter Mar 29 at 18:26
add comment

Solved

The problem was not inside this function.

The problem was the trigger being defined with an AFTER rather than BEFORE.

I had this…

CREATE TRIGGER set_flags_on_recipe_trigger_
AFTER INSERT OR UPDATE ON recipe_
FOR EACH ROW EXECUTE PROCEDURE set_flags_on_recipe_trigger__function_();

rather than…

CREATE TRIGGER set_flags_on_recipe_trigger_
BEFORE INSERT OR UPDATE ON recipe_
FOR EACH ROW EXECUTE PROCEDURE set_flags_on_recipe_trigger__function_();

Lesson Learned

If the function seems to be running correctly, confirmed by use of RAISE NOTICE, but the row remains unaffected, check the AFTER versus BEFORE.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.