Home Postgres: UPDATE statement has no effect
Reply: 1

Postgres: UPDATE statement has no effect

El Goodo
1#
El Goodo Published in 2017-09-13 15:15:50Z

I am using PostgreSQL 9.6.1. Can anyone explain why the update statement below is having no effect?

db=> SELECT name, contact_type_id, is_default, mod_ts FROM master.contact_type
WHERE contact_type_id = 'c255e0f7-16b1-4f12-9ae4-8fca66073ad3';
  name   |           contact_type_id            | is_default |           mod_ts
---------+--------------------------------------+------------+----------------------------
 Bar Foo | c255e0f7-16b1-4f12-9ae4-8fca66073ad3 | f          | 2017-09-13 14:40:00.961006
(1 row)

db=> UPDATE master.contact_type
SET name = 'Foobar Foo'
WHERE contact_type_id = 'c255e0f7-16b1-4f12-9ae4-8fca66073ad3';
UPDATE 1
db=> SELECT name, contact_type_id, is_default, mod_ts FROM master.contact_type
WHERE contact_type_id = 'c255e0f7-16b1-4f12-9ae4-8fca66073ad3';
  name   |           contact_type_id            | is_default |           mod_ts
---------+--------------------------------------+------------+----------------------------
 Bar Foo | c255e0f7-16b1-4f12-9ae4-8fca66073ad3 | f          | 2017-09-13 14:45:15.257798
(1 row)

As you can see above, the value of the name field is not changed. I also see no errors being raised.

Here is the description of the contact_type table:

db=> \d master.contact_type
                      Table "master.contact_type"
     Column      |            Type             |       Modifiers
-----------------+-----------------------------+------------------------
 contact_type_id | uuid                        | not null
 gid_number      | uuid                        | not null
 name            | character varying(75)       | not null
 mod_ts          | timestamp without time zone | not null default now()
 is_default      | boolean                     | not null default false
Indexes:
    "pk_contact_type" PRIMARY KEY, btree (contact_type_id) WITH (fillfactor='100')
    "unq_contact_type__gid_number__name" UNIQUE CONSTRAINT, btree (gid_number, name) WITH (fillfactor='90')
    "idx_contact_type__gid_number" btree (gid_number) WITH (fillfactor='70')
    "idx_contact_type__name" btree (gid_number, lower(name::text)) WITH (fillfactor='70')
Referenced by:
    TABLE "master.contact" CONSTRAINT "fk_contact__contact_type" FOREIGN KEY (contact_type_id) REFERENCES master.contact_type(contact_type_id) ON DELETE RESTRICT
Triggers:
    trg_contact_type_delete_update__preserve_default BEFORE DELETE OR UPDATE ON master.contact_type FOR EACH ROW EXECUTE PROCEDURE master.check_contact_type__is_default()
    trg_master_contact_type__mod_ts BEFORE INSERT OR UPDATE ON master.contact_type FOR EACH ROW EXECUTE PROCEDURE common.update_stamp()

Here are the trigger function definitions:

CREATE FUNCTION master.check_contact_type__is_default() RETURNS TRIGGER AS $check_contact_type__is_default$
    BEGIN
        IF OLD.is_default THEN
            RAISE EXCEPTION 'Cannot update or delete default Contact Types shipped with the product.';
        END IF;
        RETURN OLD;
    END;
$check_contact_type__is_default$ LANGUAGE plpgsql;

CREATE FUNCTION common.update_stamp() RETURNS TRIGGER AS $update_stamp$
BEGIN
    NEW.mod_ts = current_timestamp;
    RETURN NEW;
END;
$update_stamp$ LANGUAGE plpgsql;
JGH
2#
JGH Reply to 2017-09-13 15:23:07Z

The trigger master.check_contact_type__is_default() returns the OLD values, so the ones before the update. You want to return the NEW values.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.307044 second(s) , Gzip On .

© 2016 Powered by cudou.com design MATCHINFO