Postgres function for updating table b when a column in table a is updated

Question

I am new to postgres triggers and functions and I am currently having an issue with setting up a trigger on table A to update table B when a boolean column in table A is switches from true to false or vice versa.

Table B is a "mapping" table that has values from Table A / Table B and other tables that don't have any reliable means of linkage and thus I am creating triggers to propagate data to this mapping table.

Trigger and Trigger function below:

--TRIGGER FUNCTION TO UPDATE CCM AFTER ENVIRONMENT TYPE IS CHANGED
--  THIS SHOULD BE A TRIGGER BASED FUNCTION - NO INPUT WILL BE PASSED
--  THIS WILL NEED TO BE DONE POST UPDATE ON THE RECORD
CREATE OR REPLACE FUNCTION live.ccmu4statust()
  RETURNS TRIGGER AS $$
  BEGIN
    CASE WHEN TG_ARGV[0] = 'true' THEN
      UPDATE live.customer_control_mapping set enabled = true where customer_env_name = TG_ARGV[0];
    ELSE
      UPDATE live.customer_control_mapping set enabled = false where customer_env_name = TG_ARGV[0];
    END CASE;
  END;
$$ LANGUAGE plpgsql;

--TRIGGER ON LIVE.CUSTOMER_SPEC
--  SHOULD ONLY EXECUTE WHEN A CUSTOMER_ENV_STATUS IS UPDATED
-- RESULTED IN A CASTING ISSUE FOR BOOLEAN VALUE
CREATE TRIGGER ccmu4statusJello
AFTER UPDATE
ON live.customer_spec
FOR EACH ROW
EXECUTE PROCEDURE live.ccmu4statust('customer_env_name','customer_enabled');

The error that I am getting is:
Error: ERROR: control reached end of trigger procedure without RETURN
  Where: PL/pgSQL function live.ccmu4statust()
SQLState:  2F005
ErrorCode: 0

Any help would be greatly appreciated, Please note the mapping table is required for development functionality and I can't remove it, hence I need the updates working.

A manual function worked for me (it is below) - it was done as a test.

CREATE OR REPLACE FUNCTION live.ccmu4status(custEnvName text,customer_enabled boolean) RETURNS VOID AS $$ BEGIN UPDATE live.customer_control_mapping set customer_enabled = $2 where customer_env_name = $1; END; $$ LANGUAGE plpgsql;

Thanks again and have a great day!

Patrick Patrick · Accepted Answer · 2016-12-14T00:43:43

Basically, what you need to do is the following:

CREATE FUNCTION live.ccmu4statust() RETURNS trigger AS $$
BEGIN
    IF NEW.env_status != OLD.env_status THEN
        UPDATE live.customer_control_mapping
        SET enabled = NEW.env_status
        WHERE customer_env_name = NEW.env_name;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER ccmu4statusJello
AFTER UPDATEON live.customer_spec
FOR EACH ROW EXECUTE PROCEDURE live.ccmu4statust();

In trigger functions you can make use of the implicitly defined variables OLD and NEW which represent the original and the updated row, respectively. Trigger functions always require a RETURN statement; you should usually RETURN NEW to make the update succeed (although this here is an AFTER UPDATE trigger so you could also just RETURN but better to be consistent).

Note also that this trigger function uses data from columns in table live.customer_spec only, so no environmental variables are used (which seems to be the case in your code). Much cleaner and much easier to maintain.

Postgres function for updating table b when a column in table a is updated

1 Answers