Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 127FC4765B8 for ; Wed, 28 Aug 2002 10:23:01 -0400 (EDT) Received: from aragorn.reaumur.absolight.net (ATuileries-113-2-1-126.abo.wanadoo.fr [80.11.216.126]) by postgresql.org (Postfix) with ESMTP id F03AA475A93 for ; Wed, 28 Aug 2002 10:22:43 -0400 (EDT) Received: from andromede.reaumur.absolight.net (andromede.reaumur.absolight.net [212.43.217.61]) by aragorn.reaumur.absolight.net (Postfix) with ESMTP id B6A3083B8; Wed, 28 Aug 2002 16:22:40 +0200 (CEST) Date: Wed, 28 Aug 2002 16:22:37 +0200 From: Mathieu Arnold To: josh@agliodbs.com Cc: pgsql-sql@postgresql.org Subject: Re: triggers and plpgsql question Message-ID: <454316312.1030551754@andromede.reaumur.absolight.net> In-Reply-To: <169617531.1030524133@sauron> References: <374862253.1030472287@andromede.reaumur.absolight.net> <200208271538.45877.josh@agliodbs.com> <169617531.1030524133@sauron> X-Mailer: Mulberry/3.0.0b1 (Win32) X-wazaaa: True, true MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="==========454331834==========" X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200208/381 X-Sequence-Number: 9227 --==========454331834========== Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline --On mercredi 28 ao=FBt 2002 08:42 +0200 Mathieu Arnold wrote: >=20 >=20 > --On mardi 27 ao=FBt 2002 15:38 -0700 Josh Berkus wro= te: >=20 >>=20 >> Mathieu, >>=20 >>> The thing I need, is to be able to know what does NEW contains, and I >>> have not found out any mean to do so. If it's not possible to do so, >>> I'll write a function per table, but for the beauty of all this, I would >>> have liked to do it the way above. >>=20 >> You can't do this in PL/pgSQL. See the online documentation on writing= =20 >> triggers in C; that is the only way to get what you want. >=20 > So, if I want to avoid C, I'll have to write a function per table. I'll > have a look at SPI (as I believe after a short readout of the doc, I'll > need it). After a few hours of work, here is what I did. What it does is to log everything that gets inserted, deleted or updated into a table. I post it here because I believe that someone else might be interested. --=20 Mathieu Arnold --==========454331834========== Content-Type: application/octet-stream; name="triggers.c" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="triggers.c"; size=4291 LyoKICoJdmltOiB0cz00IHNoaWZ0d2lkdGg9NAogKgkkQWJzbzogYWJzby93 ZWIvZ2VzdGlvbi9zcWwvQy90cmlnZ2Vycy5jLHYgMS4yIDIwMDIvMDgvMjgg MTQ6MDk6MTAgbWF0IEV4cCAkCiAqLwoKI2luY2x1ZGUgPGV4ZWN1dG9yL3Nw aS5oPgkJLyogdGhpcyBpcyB3aGF0IHlvdSBuZWVkIHRvIHdvcmsgd2l0aCBT UEkgKi8KI2luY2x1ZGUgPGNvbW1hbmRzL3RyaWdnZXIuaD4JLyogLSItIGFu ZCB0cmlnZ2VycyAqLwoKI2lmZGVmIERCRwojZGVmaW5lIEQoeC4uLikgeyBl bG9nKE5PVElDRSwgIyN4KTsgfQojZWxzZQojZGVmaW5lIEQoeC4uLikgeyB9 OwojZW5kaWYKCiNkZWZpbmUgQ09OVEVOVU1BWCA0MDk2CiNkZWZpbmUgUVVF UllNQVggKENPTlRFTlVNQVggKyAyNTYpCiNkZWZpbmUgUVVFUlkgIklOU0VS VCBJTlRPIGhpc3RvcmlxdWUgKG9wZXJhdGlvbiwgXCJ0YWJsZVwiLCBjb250 ZW51KSBWQUxVRVMgKCVzLCAlcywgJXMpIgoKZXh0ZXJuIERhdHVtIGhpc3Rv cmlzYXRpb24oUEdfRlVOQ1RJT05fQVJHUyk7CnN0YXRpYyB2b2lkIGFwcGVu ZF9jb250ZW51KGNoYXIgKiosIGNoYXIgKiwgY2hhciAqKTsKc3RhdGljIHZv aWQgYXBwZW5kX2NvbnRlbnVfbmV3KGNoYXIgKiosIGNoYXIgKiwgY2hhciAq LCBjaGFyICopOwpzdGF0aWMgY2hhciAqcXVvdGVfaXQoY2hhciAqKTsKClBH X0ZVTkNUSU9OX0lORk9fVjEoaGlzdG9yaXNhdGlvbik7CgpEYXR1bQpoaXN0 b3Jpc2F0aW9uKFBHX0ZVTkNUSU9OX0FSR1MpCnsKCVRyaWdnZXJEYXRhICp0 cmlnZGF0YSA9IChUcmlnZ2VyRGF0YSAqKWZjaW5mby0+Y29udGV4dDsKCWNo YXIJICAgKnJlbG5hbWU7CQkvKiB0cmlnZ2VyZWQgcmVsYXRpb24gbmFtZSAq LwoJUmVsYXRpb24JcmVsOwkJCS8qIHRyaWdnZXJlZCByZWxhdGlvbiAqLwoJ SGVhcFR1cGxlCXJldHR1cGxlID0gTlVMTDsKCVR1cGxlRGVzYwl0dXBkZXNj OwkJLyogdHVwbGUgZGVzY3JpcHRpb24gKi8KCglpbnQJCQlpOwoJY2hhcgkg ICAqYXR0cm5hbWUsCgkJCSAgICphdHRydmFsLAoJCQkgICAqYXR0cnZhbDIs CgkJCSAgICpxdWVyeSwKCQkJICAgKnR5cGUsCgkJCSAgICpjb250ZW51OwoK CWlmICghQ0FMTEVEX0FTX1RSSUdHRVIoZmNpbmZvKSkKCQllbG9nKEVSUk9S LCAiaGlzdG9yaXNhdGlvbjogbm90IGZpcmVkIGJ5IHRyaWdnZXIgbWFuYWdl ciIpOwoJaWYgKFRSSUdHRVJfRklSRURfRk9SX1NUQVRFTUVOVCh0cmlnZGF0 YS0+dGdfZXZlbnQpKQoJCWVsb2coRVJST1IsICJoaXN0b3Jpc2F0aW9uOiBj YW4ndCBwcm9jZXNzIFNUQVRFTUVOVCBldmVudHMiKTsKCWlmIChUUklHR0VS X0ZJUkVEX0JFRk9SRSh0cmlnZGF0YS0+dGdfZXZlbnQpKQoJCWVsb2coRVJS T1IsICJoaXN0b3Jpc2F0aW9uOiBtdXN0IGJlIGZpcmVkIGFmdGVyIGV2ZW50 Iik7CgoJcmVsID0gdHJpZ2RhdGEtPnRnX3JlbGF0aW9uOwoJcmVsbmFtZSA9 IFNQSV9nZXRyZWxuYW1lKHJlbCk7Cgl0dXBkZXNjID0gcmVsLT5yZF9hdHQ7 CgoJY29udGVudSA9IChjaGFyICopcGFsbG9jKENPTlRFTlVNQVggKyAxKTsK CWJ6ZXJvKGNvbnRlbnUsIENPTlRFTlVNQVggKyAxKTsKCglpZiAoVFJJR0dF Ul9GSVJFRF9CWV9JTlNFUlQodHJpZ2RhdGEtPnRnX2V2ZW50KSkKCXsKCQl0 eXBlID0gc3RyZHVwKCJJTlNFUlQiKTsKCQlyZXR0dXBsZSA9IHRyaWdkYXRh LT50Z190cmlndHVwbGU7CgoJCWZvciAoaSA9IDE7IChhdHRybmFtZSA9IFNQ SV9mbmFtZSh0dXBkZXNjLCBpKSkgIT0gTlVMTDsgaSsrKQoJCXsKCQkJYXR0 cnZhbCA9IFNQSV9nZXR2YWx1ZShyZXR0dXBsZSwgdHVwZGVzYywgaSk7CgkJ CWFwcGVuZF9jb250ZW51KCZjb250ZW51LCBhdHRybmFtZSwgYXR0cnZhbCk7 CgkJCWlmIChhdHRydmFsICE9IE5VTEwpCgkJCQlwZnJlZShhdHRydmFsKTsK CQkJcGZyZWUoYXR0cm5hbWUpOwoJCX0KCX0KCWVsc2UgaWYgKFRSSUdHRVJf RklSRURfQllfVVBEQVRFKHRyaWdkYXRhLT50Z19ldmVudCkpCgl7CgkJdHlw ZSA9IHN0cmR1cCgiVVBEQVRFIik7CgkJcmV0dHVwbGUgPSB0cmlnZGF0YS0+ dGdfbmV3dHVwbGU7CgkJYXBwZW5kX2NvbnRlbnUoJmNvbnRlbnUsIFNQSV9m bmFtZSh0dXBkZXNjLCAxKSwgU1BJX2dldHZhbHVlKHJldHR1cGxlLCB0dXBk ZXNjLCAxKSk7CgkJZm9yIChpID0gMjsgKGF0dHJuYW1lID0gU1BJX2ZuYW1l KHR1cGRlc2MsIGkpKSAhPSBOVUxMOyBpKyspCgkJewoJCQlhdHRydmFsID0g U1BJX2dldHZhbHVlKHJldHR1cGxlLCB0dXBkZXNjLCBpKTsKCQkJYXR0cnZh bDIgPSBTUElfZ2V0dmFsdWUodHJpZ2RhdGEtPnRnX3RyaWd0dXBsZSwgdHVw ZGVzYywgaSk7CgkJCWlmIChzdHJjbXAoYXR0cnZhbCwgYXR0cnZhbDIpICE9 IDApCgkJCQlhcHBlbmRfY29udGVudV9uZXcoJmNvbnRlbnUsIGF0dHJuYW1l LCBhdHRydmFsMiwgYXR0cnZhbCk7CgkJCWVsc2UKCQkJCWFwcGVuZF9jb250 ZW51KCZjb250ZW51LCBhdHRybmFtZSwgYXR0cnZhbCk7CgkJCWlmIChhdHRy dmFsMiAhPSBOVUxMKQoJCQkJcGZyZWUoYXR0cnZhbDIpOwoJCQlpZiAoYXR0 cnZhbCAhPSBOVUxMKQoJCQkJcGZyZWUoYXR0cnZhbCk7CgkJCXBmcmVlKGF0 dHJuYW1lKTsKCQl9Cgl9CgllbHNlCgl7CgkJdHlwZSA9IHN0cmR1cCgiREVM RVRFIik7CgkJcmV0dHVwbGUgPSB0cmlnZGF0YS0+dGdfdHJpZ3R1cGxlOwoJ CWZvciAoaSA9IDE7IChhdHRybmFtZSA9IFNQSV9mbmFtZSh0dXBkZXNjLCBp KSkgIT0gTlVMTDsgaSsrKQoJCXsKCQkJYXR0cnZhbCA9IFNQSV9nZXR2YWx1 ZShyZXR0dXBsZSwgdHVwZGVzYywgaSk7CgkJCWFwcGVuZF9jb250ZW51KCZj b250ZW51LCBhdHRybmFtZSwgYXR0cnZhbCk7CgkJCWlmIChhdHRydmFsICE9 IE5VTEwpCgkJCQlwZnJlZShhdHRydmFsKTsKCQkJcGZyZWUoYXR0cm5hbWUp OwoJCX0KCX0KCgkvKiBvbiBlbmxldmUgbGUgIiwgIiBxdWkgc2UgdHJvdXZl IGEgbGEgZmluICovCgkqKGNvbnRlbnUgKyBzdHJsZW4oY29udGVudSkgLSAy KSA9ICdcMCc7CgoJU1BJX2Nvbm5lY3QoKTsKCXF1ZXJ5ID0gKGNoYXIgKilw YWxsb2MoUVVFUllNQVgpOwoJc25wcmludGYocXVlcnksIFFVRVJZTUFYLCBR VUVSWSwgcXVvdGVfaXQodHlwZSksIHF1b3RlX2l0KHJlbG5hbWUpLCBxdW90 ZV9pdChjb250ZW51KSk7CglTUElfZXhlYyhxdWVyeSwgMSk7CglTUElfZmlu aXNoKCk7CgoJcGZyZWUoY29udGVudSk7CglwZnJlZShyZWxuYW1lKTsKCgly ZXR1cm4gUG9pbnRlckdldERhdHVtKHJldHR1cGxlKTsKfQoKCnN0YXRpYyBj aGFyICoKcXVvdGVfaXQoY2hhciAqbHB0cikKewoJY2hhcgkgICAqcmVzdWx0 ID0gTlVMTCwKCQkJICAgKmQgPSBOVUxMLAoJCQkgICAqYzsKCWludAkJCWxl biA9IDA7CgoJaWYgKGxwdHIgPT0gTlVMTCkKCQlyZXN1bHQgPSBzdHJkdXAo Ik5VTEwiKTsKCWVsc2UKCXsKCQlsZW4gPSBzdHJsZW4obHB0cik7CgkJLyog MyA6ICcgZHUgZOlidXQgZXQgZmluICsgXDAgKi8KCQlkID0gKGNoYXIgKilw YWxsb2MobGVuICogMiArIDMpOwoJCXJlc3VsdCA9IGQ7CgkJYnplcm8oZCwg bGVuICogMiArIDMpOwoJCWMgPSBscHRyOwoKCQkqZCsrID0gJ1wnJzsKCQl3 aGlsZSAobGVuLS0gPiAwKQoJCXsKCQkJaWYgKCpscHRyID09ICdcJycpCgkJ CQkqZCsrID0gJ1wnJzsKCQkJaWYgKCpscHRyID09ICdcXCcpCgkJCQkqZCsr ID0gJ1xcJzsKCQkJKmQrKyA9ICpscHRyKys7CgkJfQoJCSpkKysgPSAnXCcn OwoJCSpkID0gJ1wwJzsKCX0KCglyZXR1cm4gcmVzdWx0Owp9CgpzdGF0aWMg dm9pZAphcHBlbmRfY29udGVudShjaGFyICoqY29udGVudSwgY2hhciAqcXVp LCBjaGFyICpxdW9pKQp7CglzdHJuY2F0KCpjb250ZW51LCBxdWksIENPTlRF TlVNQVgpOwoJc3RybmNhdCgqY29udGVudSwgIj0iLCBDT05URU5VTUFYKTsK CXN0cm5jYXQoKmNvbnRlbnUsIHF1b3RlX2l0KHF1b2kpLCBDT05URU5VTUFY KTsKCXN0cm5jYXQoKmNvbnRlbnUsICIsICIsIENPTlRFTlVNQVgpOwp9Cgpz dGF0aWMgdm9pZAphcHBlbmRfY29udGVudV9uZXcoY2hhciAqKmNvbnRlbnUs IGNoYXIgKnF1aSwgY2hhciAqcXVvaSwgY2hhciAqbmV3KQp7CglzdHJuY2F0 KCpjb250ZW51LCBxdWksIENPTlRFTlVNQVgpOwoJc3RybmNhdCgqY29udGVu dSwgIiAiLCBDT05URU5VTUFYKTsKCXN0cm5jYXQoKmNvbnRlbnUsIHF1b3Rl X2l0KHF1b2kpLCBDT05URU5VTUFYKTsKCXN0cm5jYXQoKmNvbnRlbnUsICIg LT4gIiwgQ09OVEVOVU1BWCk7CglzdHJuY2F0KCpjb250ZW51LCBxdW90ZV9p dChuZXcpLCBDT05URU5VTUFYKTsKCXN0cm5jYXQoKmNvbnRlbnUsICIsICIs IENPTlRFTlVNQVgpOwp9Cg== --==========454331834==========--