Thread: Question on Rules
I am creating a rule which will copy a record when it is changed to a
audittable. My question is that the first column is a UUID data type
with a defined as auditaccessorid uuid DEFAULT
isscontrib.uuid_generate_v4() NOT NULL,
Right now I've got that set to NULL to allow the parser to "compile".
What value should I have in here since I want a newly created UUID?
CREATE RULE log_accessor AS ON UPDATE TO iss.accessor
WHERE NEW.* <> OLD.*
DO INSERT INTO iss.auditaccessor VALUES (NULL,
'C',
new.loaddtlid,
new.seqno,
new.billable,
new.payind,
new.code,
new.description,
new.ref,
new.tractororcarrierflag,
new.tractororcarrierno,
new.tractorpct,
new.charge,
new.type,
new.checkdate,
new.checkno,
new.processed,
new.itemflag,
new.tractortermloc,
new.cost,
new.batchno,
new.editdatetime,
new.edituser);
Best Regards,
Michael Gould
Intermodal Software Solutions, LLC
904-226-0978
-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of
mgould@isstrucksoftware.net
Sent: Saturday, February 18, 2012 5:17 AM
To: pgsql-general@postgresql.org
Subject: [GENERAL] Question on Rules
I am creating a rule which will copy a record when it is changed to a audittable. My question is that the first column
isa UUID data type with a defined as auditaccessorid uuid DEFAULT
isscontrib.uuid_generate_v4() NOT NULL,
Right now I've got that set to NULL to allow the parser to "compile".
What value should I have in here since I want a newly created UUID?
CREATE RULE log_accessor AS ON UPDATE TO iss.accessor
WHERE NEW.* <> OLD.*
DO INSERT INTO iss.auditaccessor VALUES (NULL,
'C',
new.loaddtlid,
new.seqno,
new.billable,
new.payind,
new.code,
new.description,
new.ref,
new.tractororcarrierflag,
new.tractororcarrierno,
new.tractorpct,
new.charge,
new.type,
new.checkdate,
new.checkno,
new.processed,
new.itemflag,
new.tractortermloc,
new.cost,
new.batchno,
new.editdatetime,
new.edituser);
Best Regards,
Michael Gould
Intermodal Software Solutions, LLC
904-226-0978
--------------------------------------------------------------------
INSERT INTO table (serial_col1) VALUES (DEFAULT);
Also, I presume you have a good reason for using a RULE instead of a TRIGGER? If not you should default to a TRIGGER
forthis kind of behavior.
David J.
<table align="center" bgcolor="#ffffff" border="0" cellpadding="0" cellspacing="0" style="empty-cells: show;" width="100%"><tr><td;=";" </td="</td" align="left" background="cid:top@58571d753b9e554223bb4a1866f4c8b1" colspan="1" height="72"style="text-align: left; background-repeat: no-repeat;"></td></tr><tr><td style="vertical-align: top;"><div style="padding:5px; overflow-x: auto;"></div></td></tr></table><span style="font-family:Verdana; color:#000000; font-size:10pt;"><div>David,</div><div> </div><div>Arerules deprecated?<br /></div><div><br /></div><div>Michael Gould</div><div>IntermodalSoftware Solutions, LLC</div><div>904-226-0978</div><div> </div><div> </div><blockquote id="replyBlockquote"style="BORDER-LEFT: blue 2px solid; PADDING-LEFT: 8px; FONT-FAMILY: verdana; COLOR: black; MARGIN-LEFT:8px; FONT-SIZE: 10pt" webmail="1"><div id="wmQuoteWrapper">-------- Original Message --------<br />Subject: Re:[GENERAL] Question on Rules<br />From: "David Johnston" <<a href="mailto:polobo@yahoo.com">polobo@yahoo.com</a>><br/>Date: Sun, February 19, 2012 9:16 pm<br />To: <<a href="mailto:mgould@isstrucksoftware.net">mgould@isstrucksoftware.net</a>>,<<a href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>><br/><br />-----Original Message-----<br />From:<a href="mailto:pgsql-general-owner@postgresql.org">pgsql-general-owner@postgresql.org</a> [<a href="mailto:pgsql-general-owner@postgresql.org">mailto:pgsql-general-owner@postgresql.org</a>]On Behalf Of <a href="mailto:mgould@isstrucksoftware.net">mgould@isstrucksoftware.net</a><br/>Sent: Saturday, February 18, 2012 5:17 AM<br/>To: <a href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a><br />Subject: [GENERAL] Questionon Rules<br /><br />I am creating a rule which will copy a record when it is changed to a audittable. My questionis that the first column is a UUID data type with a defined as auditaccessorid uuid DEFAULT<br />isscontrib.uuid_generate_v4()NOT NULL,<br /><br />Right now I've got that set to NULL to allow the parser to "compile".<br />What value should I have in here since I want a newly created UUID?<br /><br />CREATE RULE log_accessor ASON UPDATE TO iss.accessor<br />WHERE NEW.* <> OLD.*<br />DO INSERT INTO iss.auditaccessor VALUES (NULL,<br />'C',<br/>new.loaddtlid, <br />new.seqno, <br />new.billable,<br />new.payind,<br />new.code,<br />new.description, <br/>new.ref,<br />new.tractororcarrierflag, <br />new.tractororcarrierno,<br />new.tractorpct,<br />new.charge,<br />new.type,<br/>new.checkdate,<br />new.checkno,<br />new.processed,<br />new.itemflag, <br />new.tractortermloc,<br />new.cost,<br/>new.batchno,<br />new.editdatetime, <br />new.edituser);<br /><br />Best Regards,<br /><br />Michael Gould<br/>Intermodal Software Solutions, LLC<br />904-226-0978<br /><br /><br />--------------------------------------------------------------------<br/><br />INSERT INTO table (serial_col1) VALUES (DEFAULT);<br/><br />Also, I presume you have a good reason for using a RULE instead of a TRIGGER? If not you should defaultto a TRIGGER for this kind of behavior.<br /><br />David J.<br /><br /><br /><br />-- <br />Sent via pgsql-generalmailing list (<a href="mailto:pgsql-general@postgresql.org">pgsql-general@postgresql.org</a>)<br />To make changesto your subscription:<br /><a href="http://www.postgresql.org/mailpref/pgsql-general">http://www.postgresql.org/mailpref/pgsql-general</a><br /></div></blockquote></span>
On 02/20/12 9:07 AM, mgould@isstrucksoftware.net wrote: > Are rules deprecated? rules are very tricky, and primarily exist now for internal use. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
Thanks I will change to a trigger Best Regards Mike Gould Sent from Samsung mobile David Johnston <polobo@yahoo.com> wrote: >-----Original Message----- >From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of mgould@isstrucksoftware.net >Sent: Saturday, February 18, 2012 5:17 AM >To: pgsql-general@postgresql.org >Subject: [GENERAL] Question on Rules > >I am creating a rule which will copy a record when it is changed to a audittable. My question is that the first columnis a UUID data type with a defined as auditaccessorid uuid DEFAULT >isscontrib.uuid_generate_v4() NOT NULL, > >Right now I've got that set to NULL to allow the parser to "compile". >What value should I have in here since I want a newly created UUID? > >CREATE RULE log_accessor AS ON UPDATE TO iss.accessor > WHERE NEW.* <> OLD.* > DO INSERT INTO iss.auditaccessor VALUES (NULL, > 'C', > new.loaddtlid, > new.seqno, > new.billable, > new.payind, > new.code, > new.description, > new.ref, > new.tractororcarrierflag, > new.tractororcarrierno, > new.tractorpct, > new.charge, > new.type, > new.checkdate, > new.checkno, > new.processed, > new.itemflag, > new.tractortermloc, > new.cost, > new.batchno, > new.editdatetime, > new.edituser); > >Best Regards, > >Michael Gould >Intermodal Software Solutions, LLC >904-226-0978 > > >-------------------------------------------------------------------- > >INSERT INTO table (serial_col1) VALUES (DEFAULT); > >Also, I presume you have a good reason for using a RULE instead of a TRIGGER? If not you should default to a TRIGGER forthis kind of behavior. > >David J. > > >