Thread: Bug in plpgsql, when using NEW with composite field value.

U1FMOgoKQ1JFQVRFIE9SIFJFUExBQ0UgRlVOQ1RJT04gImJ1Z193aXRoX3Ry
aWdnZXJzIiAoKSBSRVRVUk5TIHRyaWdnZXIgQVMKJGJvZHkkCkJFR0lOCglQ
RVJGT1JNIENPQUxFU0NFKE5FVy5zb21lX2NvbXBvc2l0ZV9maWVsZC5maWVs
ZCwgVFJVRSk7CkVORDsKJGJvZHkkCkxBTkdVQUdFICdwbHBnc3FsJyBWT0xB
VElMRSBDQUxMRUQgT04gTlVMTCBJTlBVVCBTRUNVUklUWSBJTlZPS0VSOwoK
RXJyb3I6CgpFUlJPUjogIE5FVyB1c2VkIGluIHF1ZXJ5IHRoYXQgaXMgbm90
IGluIGEgcnVsZQpRVUVSWTogIFNFTEVDVCAgQ09BTEVTQ0UoTkVXLnNvbWVj
b21wb3NpdGVmaWVsZC5maWVsZCwgVFJVRSkKQ09OVEVYVDogIFNRTCBzdGF0
ZW1lbnQgaW4gUEwvUGdTUUwgZnVuY3Rpb24gImJ1Z193aXRoX3RyaWdnZXJz
IiBuZWFyIGxpbmUgMgoKCioqKioqKioqKiogRVJST1IgKioqKioqKioqKgoK
RVJST1I6IE5FVyB1c2VkIGluIHF1ZXJ5IHRoYXQgaXMgbm90IGluIGEgcnVs
ZQpTUUwgc3RhdGU6IDQyNjAxCuvPztTFy9PUOlNRTCBzdGF0ZW1lbnQgaW4g
UEwvUGdTUUwgZnVuY3Rpb24gImJ1Z193aXRoX3RyaWdnZXJzIiBuZWFyIGxp
bmUgMgo=

SGVsbG8KCnlvdSBoYXZlIHRvIHVzZSBwYXJlbnRoZXNpcywgYmVjYXVzZSBw
YXJzZXIgZG9uJ3QgZGlzdGluZ3Vpc2ggYmV0d2Vlbgp2YXJpYW50cyBzY2hl
bWEudGFibGUuY29sdW1uIGFuZCB2YXJpYWJsZS5maWVsZC5hdHRyaWIKCmV4
YW1wbGU6Cgpwb3N0Z3Jlcz0jIGNyZWF0ZSBvciByZXBsYWNlIGZ1bmN0aW9u
IHRyZ2JvZHkoKSByZXR1cm5zIHRyaWdnZXIgYXMgJCQKYmVnaW4KICByYWlz
ZSBub3RpY2UgJyUnLCAobmV3LmEpLmE7CiAgcmV0dXJuIG5ldzsKZW5kICQk
IGxhbmd1YWdlIHBscGdzcWw7CkNSRUFURSBGVU5DVElPTgpwb3N0Z3Jlcz0j
IGNyZWF0ZSB0eXBlIHh4IGFzIChhIGludGVnZXIsIGIgaW50ZWdlcik7CkNS
RUFURSBUWVBFCnBvc3RncmVzPSMgY3JlYXRlIHRhYmxlIGcoYSB4eCk7CkNS
RUFURSBUQUJMRQpwb3N0Z3Jlcz0jIGNyZWF0ZSB0cmlnZ2VyIGdnZyAgYmVm
b3JlIGluc2VydCBvbiBnIGZvciBlYWNoIHJvdyBleGVjdXRlCnByb2NlZHVy
ZSB0cmdib2R5KCk7CkNSRUFURSBUUklHR0VSCnBvc3RncmVzPSMgaW5zZXJ0
IGludG8gZyB2YWx1ZXMoJygxMCwyMCknKTsKTk9USUNFOiAgMTAKSU5TRVJU
IDAgMQoKcmVnYXJkcwpQYXZlbCBTdGVodWxlCgoyMDA4LzEyLzEwIE9sZWcg
U2Vyb3YgPHNlcm92b3ZAZ21haWwuY29tPjoKPiBTUUw6Cj4KPiBDUkVBVEUg
T1IgUkVQTEFDRSBGVU5DVElPTiAiYnVnX3dpdGhfdHJpZ2dlcnMiICgpIFJF
VFVSTlMgdHJpZ2dlciBBUwo+ICRib2R5JAo+IEJFR0lOCj4gICAgICAgIFBF
UkZPUk0gQ09BTEVTQ0UoTkVXLnNvbWVfY29tcG9zaXRlX2ZpZWxkLmZpZWxk
LCBUUlVFKTsKPiBFTkQ7Cj4gJGJvZHkkCj4gTEFOR1VBR0UgJ3BscGdzcWwn
IFZPTEFUSUxFIENBTExFRCBPTiBOVUxMIElOUFVUIFNFQ1VSSVRZIElOVk9L
RVI7Cj4KPiBFcnJvcjoKPgo+IEVSUk9SOiAgTkVXIHVzZWQgaW4gcXVlcnkg
dGhhdCBpcyBub3QgaW4gYSBydWxlCj4gUVVFUlk6ICBTRUxFQ1QgIENPQUxF
U0NFKE5FVy5zb21lY29tcG9zaXRlZmllbGQuZmllbGQsIFRSVUUpCj4gQ09O
VEVYVDogIFNRTCBzdGF0ZW1lbnQgaW4gUEwvUGdTUUwgZnVuY3Rpb24gImJ1
Z193aXRoX3RyaWdnZXJzIiBuZWFyIGxpbmUgMgo+Cj4KPiAqKioqKioqKioq
IEVSUk9SICoqKioqKioqKioKPgo+IEVSUk9SOiBORVcgdXNlZCBpbiBxdWVy
eSB0aGF0IGlzIG5vdCBpbiBhIHJ1bGUKPiBTUUwgc3RhdGU6IDQyNjAxCj4g
0JrQvtC90YLQtdC60YHRgjpTUUwgc3RhdGVtZW50IGluIFBML1BnU1FMIGZ1
bmN0aW9uICJidWdfd2l0aF90cmlnZ2VycyIgbmVhciBsaW5lIDIKPgo+IC0t
Cj4gU2VudCB2aWEgcGdzcWwtYnVncyBtYWlsaW5nIGxpc3QgKHBnc3FsLWJ1
Z3NAcG9zdGdyZXNxbC5vcmcpCj4gVG8gbWFrZSBjaGFuZ2VzIHRvIHlvdXIg
c3Vic2NyaXB0aW9uOgo+IGh0dHA6Ly93d3cucG9zdGdyZXNxbC5vcmcvbWFp
bHByZWYvcGdzcWwtYnVncwo+Cg==

"Oleg Serov" <serovov@gmail.com> writes:
> CREATE OR REPLACE FUNCTION "bug_with_triggers" () RETURNS trigger AS
> $body$
> BEGIN
>     PERFORM COALESCE(NEW.some_composite_field.field, TRUE);
> END;
> $body$
> LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;

> Error:
> ERROR:  NEW used in query that is not in a rule

I agree that this error message is pretty unhelpful, but the code is
wrong anyway.  The correct way to do this is
    PERFORM COALESCE((NEW.some_composite_field).field, TRUE);

            regards, tom lane