Postgres updates the date field when the boolean field is set to true

Question

For example, consider the table

create table foo (
  contents text NOT NULL,
  is_active boolean NOT NULL DEFAULT false,
  dt_active date
)

I insert an entry:

insert into foo (contents) values ('bar')

So far so good. Later I now want to “activate” the entry:

update foo set is_active = true

What I would like to do, if is_activechanged from falseto true, is dt_activeset to now(). For bonus points, it would be nice if is_activechanged from trueto false, dt_active is set to null, but I can live without it.

I would really like this household to be uploaded to the database, this would make the client code cleaner (since many tables (and even column tuples in the tables) could use this technique).

, ( plpgsql), "" "". .

Answer 1

CREATE OR REPLACE FUNCTION trg_update_foo() RETURNS TRIGGER AS
$BODY$
BEGIN
    IF OLD.is_active = false AND NEW.is_active = true THEN
        NEW.dt_active := now();
    ELSIF OLD.is_active = true AND NEW.is_active = false THEN
        NEW.dt_active := NULL;
    END IF;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';
CREATE TRIGGER trg_update_foo BEFORE UPDATE ON foo FOR EACH ROW EXECUTE PROCEDURE trg_update_foo();

. pl/PgSQL , - plPgSQL.

Answer 2

plpgsql , NEW OLD, .

UPDATE INSERT NEW .

UPDATE DELETE OLD .

NULL.

, , INSERT OR UPDATE, OLD.is_active NEW.is_active.

- http://www.postgresql.org/docs/8.1/interactive/plpgsql-trigger.html plpgsql, NEW OLD

Answer 3

?

. OLD, - NEW

OLD OLD.is_active. , , NEW.dt_active := now();

, hels.

Answer 4

CREATE FUNCTION actrigger() RETURNS TRIGGER AS $$ BEGIN
    IF TG_OP='UPDATE' THEN
        IF NEW.is_active THEN
            IF NOT OLD.is_active THEN
                NEW.dt_active := current_date;
            END IF;
        ELSIF NEW.dt_active IS NOT NULL
            NEW.dt_active := NULL;
        END IF;
    END IF;
    RETURN NEW;
END; $$ LANGUAGE plpgsql;
CREATE TRIGGER foobefore BEFORE UPDATE ON foo FORR EACH ROW
    EXECUTE PROCEDURE actrigger();

INSERT , , , OLD.