Received: from kieser.demon.co.uk (IDENT:root@kieser.demon.co.uk [158.152.71.154]) by hub.org (8.9.1/8.9.1) with ESMTP id TAA24950 for ; Tue, 26 Jan 1999 19:12:08 -0500 (EST) (envelope-from brad@kieser.net) Received: from kieser.net (brad@localhost [127.0.0.1]) by kieser.demon.co.uk (8.8.7/8.8.7) with ESMTP id AAA06384 for ; Wed, 27 Jan 1999 00:27:59 GMT Sender: brad@kieser.demon.co.uk Message-ID: <36AE5D8E.B4EF2808@kieser.net> Date: Wed, 27 Jan 1999 00:27:59 +0000 From: brad Organization: Kieser Consultancy Limited X-Mailer: Mozilla 4.5 [en] (X11; I; Linux 2.0.36 i586) X-Accept-Language: en MIME-Version: 1.0 To: pgsql-sql@postgresql.org Subject: Serial field and using a rule on insert Content-Type: multipart/signed; protocol="application/x-pkcs7-signature"; micalg=sha1; boundary="------------msB827A63110BEAAB9F24DBA00" This is a cryptographically signed message in MIME format. --------------msB827A63110BEAAB9F24DBA00 Content-Type: multipart/mixed; boundary="------------BE98DED27A2D6496F3EA6D69" This is a multi-part message in MIME format. --------------BE98DED27A2D6496F3EA6D69 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I have two tables, one is a master table ("visitors") and the other is a detail table ("visits"). FYI, here are the definitions: create table visitors (id serial, site text, first_visit datetime, last_visit datetime); create table visits (id int4, visit_time datetime); I want to have a rule that inserts a row into "visits" when I insert into "visitors", such as the following code: CREATE RULE record_visit_time_ins AS ON INSERT TO visitors DO INSERT into visits (id, visit_time) VALUES (NEW.id, datetime('NOW'::abstime)); The problem is that I need to record "id" in the detail table's insert. "Id" is a serial type in the master table (it is a sequential number). If I use "OLD" in the insert, the field is blank as this is an insert, but if I use "NEW", the value inserted into the detail table increments! For example, if the new value for "id" in table "visitors" is 1, then the value inserted for "id" in "visits" is 2, not 1. I assume that this is because of some quirk using the "NEW" qualifier. All that I want to do is record the id and a timestamp in "visits" for the row inserted in "visitors". Can anyone help? Thanks, Brad --------------BE98DED27A2D6496F3EA6D69 Content-Type: text/x-vcard; charset=us-ascii; name="brad.vcf" Content-Transfer-Encoding: 7bit Content-Description: Card for brad Content-Disposition: attachment; filename="brad.vcf" begin:vcard n:Kieser;Bradley x-mozilla-html:TRUE org:Kieser Consultancy Limited adr:;;;;;; version:2.1 email;internet:brad@kieser.demon.co.uk title:Director x-mozilla-cpt:;18656 fn:Bradley Kieser end:vcard --------------BE98DED27A2D6496F3EA6D69-- --------------msB827A63110BEAAB9F24DBA00 Content-Type: application/x-pkcs7-signature; name="smime.p7s" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="smime.p7s" Content-Description: S/MIME Cryptographic Signature MIIIaQYJKoZIhvcNAQcCoIIIWjCCCFYCAQExCzAJBgUrDgMCGgUAMAsGCSqGSIb3DQEHAaCC BtcwggRWMIIDv6ADAgECAhAMUYwY/zmcs6AznCvBb6ooMA0GCSqGSIb3DQEBBAUAMGIxETAP BgNVBAcTCEludGVybmV0MRcwFQYDVQQKEw5WZXJpU2lnbiwgSW5jLjE0MDIGA1UECxMrVmVy aVNpZ24gQ2xhc3MgMSBDQSAtIEluZGl2aWR1YWwgU3Vic2NyaWJlcjAeFw05ODA0MjcwMDAw MDBaFw05OTA0MjcyMzU5NTlaMIIBIDERMA8GA1UEBxMISW50ZXJuZXQxFzAVBgNVBAoTDlZl cmlTaWduLCBJbmMuMTQwMgYDVQQLEytWZXJpU2lnbiBDbGFzcyAxIENBIC0gSW5kaXZpZHVh bCBTdWJzY3JpYmVyMUYwRAYDVQQLEz13d3cudmVyaXNpZ24uY29tL3JlcG9zaXRvcnkvQ1BT IEluY29ycC4gYnkgUmVmLixMSUFCLkxURChjKTk2MTMwMQYDVQQLEypEaWdpdGFsIElEIENs YXNzIDEgLSBOZXRzY2FwZSBGdWxsIFNlcnZpY2UxFzAVBgNVBAMTDkJyYWRsZXkgS2llc2Vy MSYwJAYJKoZIhvcNAQkBFhdicmFkQGtpZXNlci5kZW1vbi5jby51azBcMA0GCSqGSIb3DQEB AQUAA0sAMEgCQQDmjClQnPCOJVQZ4RWQUYx9l56S2pUx4XxxOapyWXcFCb7kiOdtzuJ+wT+1 sXfGVawqDfpNUt+b+f5JxipOxGnFAgMBAAGjggGPMIIBizAJBgNVHRMEAjAAMIGvBgNVHSAE gacwgDCABgtghkgBhvhFAQcBATCAMCgGCCsGAQUFBwIBFhxodHRwczovL3d3dy52ZXJpc2ln bi5jb20vQ1BTMGIGCCsGAQUFBwICMFYwFRYOVmVyaVNpZ24sIEluYy4wAwIBARo9VmVyaVNp Z24ncyBDUFMgaW5jb3JwLiBieSByZWZlcmVuY2UgbGlhYi4gbHRkLiAoYyk5NyBWZXJpU2ln bgAAAAAAADARBglghkgBhvhCAQEEBAMCB4AwgYYGCmCGSAGG+EUBBgMEeBZ2ZDQ2NTJiZDYz ZjIwNDcwMjkyOTg3NjNjOWQyZjI3NTA2OWM3MzU5YmVkMWIwNTlkYTc1YmM0YmM5NzAxNzQ3 ZGE1ZDNmMjE0MWJlYWRiMmJhNjlmODMxNmJkNmU4NWFkMTE0ODljYTFiZjQ0ZmJmM2VhNDUx YjAwBgpghkgBhvhFAQYHBCIWIDdiYmRhMzhiNTEwNmZjNGNmNTA2NzBkMTVmZmNmYWU1MA0G CSqGSIb3DQEBBAUAA4GBAKEEQL75LxsdpzFEh5+FIBlQqFXzS6vTbNKA95Ep+69IzEUAiJnZ zYnChIMDnwbpL9lMgL75o5o18gYWTwRguRrFjt63pJMaVDxvDjUxKrqWSmaxCxnHjgbdRJLa 9TRH31UDgaxPkRrbBrCFTZFyftLPQrAh1ghfOMTm8f/gNDJxMIICeTCCAeKgAwIBAgIQUh81 HfJwfgArvspZhwTVOTANBgkqhkiG9w0BAQIFADBfMQswCQYDVQQGEwJVUzEXMBUGA1UEChMO VmVyaVNpZ24sIEluYy4xNzA1BgNVBAsTLkNsYXNzIDEgUHVibGljIFByaW1hcnkgQ2VydGlm aWNhdGlvbiBBdXRob3JpdHkwHhcNOTYwNjI3MDAwMDAwWhcNOTkwNjI3MjM1OTU5WjBiMREw DwYDVQQHEwhJbnRlcm5ldDEXMBUGA1UEChMOVmVyaVNpZ24sIEluYy4xNDAyBgNVBAsTK1Zl cmlTaWduIENsYXNzIDEgQ0EgLSBJbmRpdmlkdWFsIFN1YnNjcmliZXIwgZ8wDQYJKoZIhvcN AQEBBQADgY0AMIGJAoGBALYUps9N0AUN2Moj0G+qtCmSY44s+G+W1y6ddksRsTaNV8nD/RzG uv4eCLozypXqvuNbzQaot3kdRCrtc/KxUoNoEHBkkdc+a/n3XZ0UQ5tul0WYgUfRLcvdu3LX TD9xquJA8lQ5vBbuz3zsuts/bCqzFrGGEp2ukzTVuNXQ9z6pAgMBAAGjMzAxMA8GA1UdEwQI MAYBAf8CAQEwCwYDVR0PBAQDAgEGMBEGCWCGSAGG+EIBAQQEAwIBBjANBgkqhkiG9w0BAQIF AAOBgQDB+vcC51fKEXXGnAz6K3dPh0UXO+PSwdoPWDmOrpWZA6GooTj+eZqTFwuXhjnHymg0 ZrvHiEX2yAwF7r6XJe/g1G7kf512XM59uhSirguf+2dbSKVnJa8ZZIj2ctgpJ6o3EmqxKK8n gxhlbI3tQJ5NxHiohuzpLFC/pvkN27CmSjGCAVowggFWAgEBMHYwYjERMA8GA1UEBxMISW50 ZXJuZXQxFzAVBgNVBAoTDlZlcmlTaWduLCBJbmMuMTQwMgYDVQQLEytWZXJpU2lnbiBDbGFz cyAxIENBIC0gSW5kaXZpZHVhbCBTdWJzY3JpYmVyAhAMUYwY/zmcs6AznCvBb6ooMAkGBSsO AwIaBQCgfTAYBgkqhkiG9w0BCQMxCwYJKoZIhvcNAQcBMBwGCSqGSIb3DQEJBTEPFw05OTAx MjcwMDI3NTlaMB4GCSqGSIb3DQEJDzERMA8wDQYIKoZIhvcNAwICASgwIwYJKoZIhvcNAQkE MRYEFO8jF9EgrIIXpM5qJrfZRFvR8RXOMA0GCSqGSIb3DQEBAQUABEB1jJT8oU4V3Kn1H/m5 YSTtlPtE9LZhlI0jvD6iB3k1OfYyNecX+WpskRRJh1EOLB6c6oehFGpyVU/e9njERpsr --------------msB827A63110BEAAB9F24DBA00--